MySQL: Copy values from one column to another column-CodePudding

I have a table with 7 columns, the requirements is to copy or replicate the originalid of particular step with the value of 'convert' to id column. below is the sample table and expected result. Thank you in advance.

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

Expected Result:

id ------------------originalid-------------Date---------------step
111111111111       |  111111111111 |2019-01-07 02:22:30|
111111111111       |  111111111111 |2019-02-09 02:22:30|
111111111111       |  111111111111 |019-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-08-08 02:22:30|completed
111111111111       |  0001          |2019-09-07 02:22:30|

CodePudding user response：

here is sql which updates all id's with the originalid where step is 'convert', limit 1 is used to select only one row where step = 'convert'

update table1 set id =(select originalid from table1 where step = 'convert' limit 1)

select (select originalid from table1 where step = 'convert' limit 1) as id, originalid, dte, step from table1;

CodePudding user response：

Use a self-join in an UPDATE

UPDATE table1 AS t1
CROSS JOIN table1 AS t2
SET t1.id = t2.originalid
WHERE t2.step = 'convert';