I want to update the entire email column using row index [email protected] formate.

This is how the data in my table

This is the output that I want

I tried the below query but it did not give my expected output.

   ;with C as
(
  select email,row_number() over(order by id asc) as rowid
  from cus       
)
update C
set email = rowid '[email protected]'

This is not only 3 rows there are more than 500 rows in my cus table. It is better if someone can give a solution to me without looping. Please help me to create a SQL query for this. Thank you.

CodePudding user response：

Maybe this is what you're aiming 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 protected]');

Join the table you want to update (cus) with cte of C then do the update accordingly.

Here's a demo

CodePudding user response：

this seems to work, but i'm sure there is a more elegant solution without the 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 ;

complete test

-- create
CREATE TABLE cus (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL

);

-- insert
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 ;