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 ;
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.
@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.