MySQL将step为convert的行的originalid值批量更新到全表id列

现有一张6字段的数据表，需完成以下操作：提取step字段值为Convert的对应行的originalid字段值，将该值覆盖更新到全表所有行的id字段中。

建表及插入测试数据语句如下：

Create table table1 (id varchar(35), originalid varchar(35), dte datetime, step varchar(35), itemno varchar(35), originalid2 varchar(35));
INSERT INTO table1 VALUES ('111111111111','111111111111','2019-01-07 02:22:30','null','null','null'),
('111111111111','111111111111','2019-02-09 02:22:30','null','null','null'),
('111111111111','111111111111','2019-03-11 02:22:30','repair','null','null'),
('111111111111','111111111111','2019-04-07 02:22:30','null','null','null'),
('0001','111111111111','2019-04-10 02:22:30','Convert','0001','111111111111'),
('0001','0001','2019-05-12 02:22:30','null','0001','0001'),
('0001','0001','2019-06-20 02:22:30','null','0001','0001'),
('0001','0001','2019-07-25 02:22:30','null','0001','0001'),
('0001','0001','2019-08-08 02:22:30','null','0001','0001'),
('0001','0001','2019-09-07 02:22:30','Completed','0001','0001');

id ------------------originalid-------------Date---------------step
111111111111       |  111111111111 |2019-01-07 02:22:30|
111111111111       |  111111111111 |2019-02-09 02:22:30|
111111111111       |  111111111111 |2019-03-11 02:22:30 |repair
111111111111       |  111111111111 |2019-04-07 02:22:30|
111111111111       |  111111111111 |2019-04-10 02:22:30|convert
111111111111       |  0001          |2019-05-12 02:22:30|
111111111111       |  0001          |2019-06-20 02:22:30|
111111111111       |  0001          |2019-07-25 02:22:30|
111111111111       |  0001          |2019-08-08 02:22:30|
111111111111       |  0001          |2019-09-07 02:22:30|completed

MySQL 环境执行语句 #

MySQL不允许UPDATE直接引用同表子查询，需要多嵌套一层别名避免报错，加LIMIT 1是为了避免出现多条step='Convert'的记录时更新报错，若业务确定只有一条对应记录可删除该限制：

UPDATE table1
SET id = (
    SELECT originalid 
    FROM (SELECT originalid FROM table1 WHERE step = 'Convert' LIMIT 1) AS temp
);

SQL Server / Oracle 环境执行语句 #

UPDATE table1
SET id = (SELECT originalid FROM table1 WHERE step = 'Convert' AND ROWNUM = 1);

更新完成后可执行以下语句验证结果：

SELECT id, originalid, dte AS `Date`, step FROM table1 ORDER BY dte;

内容的提问来源于stack exchange，提问作者Jon