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

Question

I want to update the entire email column with the row index email@gmail.com.

This is the data in my table

id	Email
12	abc@gmail.com
23	pqr@gmail.com

This is the output I want

id	Email
12	1email@gmail.com
23	2email@gmail.com

I tried the query below but didn't get the output I expected.

;with C as
(
  select email,row_number() over(order by id asc) as rowid
  from cus
)
update C
set email = rowid 'email@gmail.com'

That's not just 3 rows, I have over 500 rows in my cus 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.

Answer 1

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 ;

Answer 2

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.