Home  >  Q&A  >  body text

Way to update entire column using row index and hardcoded value in Mysql

<p>I want to update the entire <strong>email</strong> column with the row index email@gmail.com. </p> <p>This is the data in my table</p> <table class="s-table"> <thead> <tr> <th>id</th> <th>Email</th> </tr> </thead> <tbody> <tr> <td>12</td> <td>abc@gmail.com</td> </tr> <tr> <td>23</td> <td>pqr@gmail.com</td> </tr> </tbody> </table> <p>This is the output I want</p> <table class="s-table"> <thead> <tr> <th>id</th> <th>Email</th> </tr> </thead> <tbody> <tr> <td>12</td> <td>1email@gmail.com</td> </tr> <tr> <td>23</td> <td>2email@gmail.com</td> </tr> </tbody> </table> <p>I tried the query below but didn't get the output I expected. </p> <pre class="brush:php;toolbar:false;">;with C as ( select email,row_number() over(order by id asc) as rowid from cus ) update C set email = rowid 'email@gmail.com'</pre> <p>That's not just 3 rows, I have over 500 rows in my <code>cus</code> table. It would be better if someone could give me a solution that doesn't require looping. Please help me create a SQL query. Thanks. </p>
P粉362071992P粉362071992443 days ago418

reply all(2)I'll reply

  • P粉201448898

    P粉2014488982023-08-27 14:25:34

    This seems to work, but I believe there is a more elegant solution that doesn't require using join...

    SELECT  * FROM cus ;
    
    update
    cus  inner join 
    (
    select  id ,email,row_number() over(order by id asc) as rowid
    from cus       
    )a
     on a.id = cus.id
    set cus.email = concat(a.rowid, a.email)  
    ;
    
    SELECT  * FROM cus ;

    Full Test

    -- 创建表
    CREATE TABLE cus (
      id INTEGER PRIMARY KEY,
      email TEXT NOT NULL
    
    );
    
    -- 插入数据
    INSERT INTO cus VALUES (0021, 'Clark');
    INSERT INTO cus VALUES (0402, 'Dave');
    INSERT INTO cus VALUES (005, 'Ava' );
    
    
    SELECT  * FROM cus ;
    
    update
    cus  inner join 
    (
    select  id ,email,row_number() over(order by id asc) as rowid
    from cus       
    )a
     on a.id = cus.id
    set cus.email = concat(a.rowid, a.email)  
    ;
    
    SELECT  * FROM cus ;

    reply
    0
  • P粉885035114

    P粉8850351142023-08-27 00:04:35

    Maybe this is what you want to do:

    WITH C AS
    (
      SELECT email,ROW_NUMBER() OVER(ORDER BY id ASC) AS rowid
      FROM cus       
    )
    UPDATE cus 
     JOIN C
     ON cus.email=C.email
     SET cus.email=CONCAT(rowid,'email@gmail.com');

    Connect the table you want to update (cus) with the cte of C, and then update accordingly.

    Here is a demo

    @QisM raised concerns about the syntax when email is not unique, and since the OP didn't mention it, I agree that if email is indeed not unique, this is not a solution. So I modified the syntax slightly:

    WITH C AS
    (
      SELECT id, email, ROW_NUMBER() OVER(ORDER BY id ASC) AS rowid
      FROM cus       
    )
    UPDATE cus 
     JOIN C
     ON cus.id=C.id AND cus.email=C.email
     SET cus.email=CONCAT(rowid,'email@gmail.com');

    Now cte with id, and in the JOIN C ON .. condition, I added a check that matches id . After testing, this will fix the issue if the email is not unique.

    reply
    0
  • Cancelreply