Möglichkeit, die gesamte Spalte mithilfe des Zeilenindex und des fest codierten Werts in MySQL zu aktualisieren

Question

Ich möchte die gesamte Spalte email im Format Zeilenindex + email@gmail.com aktualisieren.

Dies sind die Daten in meiner Tabelle

id	E-Mail
12	abc@gmail.com
23	pqr@gmail.com

Dies ist die Ausgabe, die ich möchte

id	E-Mail
12	1email@gmail.com
23	2email@gmail.com

Ich habe die folgende Abfrage ausprobiert, aber nicht die erwartete Ausgabe erhalten.

;mit C as
(
  Wählen Sie email,row_number() over(order by id asc) als rowid aus
  von cus
)
Update C
set email = rowid+'email@gmail.com'

Das sind nicht nur 3 Zeilen, ich habe über 500 Zeilen in meiner cus-Tabelle. Es wäre besser, wenn mir jemand eine Lösung geben könnte, die keine Schleife erfordert. Bitte helfen Sie mir, eine SQL-Abfrage zu erstellen. Danke.

Answer 1

这似乎有效，但我相信还有一种更优雅的解决方案，不需要使用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 ;

完整测试

-- 创建表
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

也许这就是你想要做的：

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');

将你想要更新的表(cus)与C的cte进行连接，然后进行相应的更新。

这里有一个演示

@QisM对于email不唯一时的语法提出了疑虑，由于OP没有提到，我同意如果email确实不唯一，这不是解决方案。因此，我对语法进行了轻微修改：

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');

现在cte带有id，并且在JOIN C ON ..条件中，我添加了匹配id的检查。经过测试，如果电子邮件不唯一，这将修复该问题。