在Mysql中使用行索引和硬編碼值更新整個列的方法

Question

我想使用行索引 email@gmail.com的格式更新整個email列。

這是我的表中的數據

id	電子郵件
12	abc@gmail.com
23	pqr@gmail.com

這是我想要的輸出

id	電子郵件
12	1email@gmail.com
23	2email@gmail.com

我嘗試了下面的查詢，但沒有得到我期望的輸出。

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

這不只是3行，我的cus表格中有500多行。如果有人可以給我一個不需要循環的解決方案，那會更好。請幫我建立一個SQL查詢。謝謝。

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的檢查。經過測試，如果電子郵件不唯一，這將修復該問題。