MySQL: Copy values from one column to another column

Question

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|

Yes, its only one row with steps='Convert'. by the way, i added new column in the sample table for itemno and originalid2. — Jon, Oct 28 '21 at 00:43
Its part of the table which i did not consider in my sample data which i think could be use as reference. — Jon, Oct 28 '21 at 00:54

glx · Answer 1 · 2021-10-27T15:00:32.730

0

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)

or

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

edited Oct 27 '21 at 15:00

answered Oct 27 '21 at 14:45

glx

81
1
6

It is not best practice to use `LIMIT` condition on update, because you never know which row is fetched first, you should be more precise in the `WHERE` condition, else you might use outdated record i.e. deleted over active record. – Cyber Oct 27 '21 at 15:11
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Cyber Oct 27 '21 at 15:12
I don't think this will work because of https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause – Barmar Oct 27 '21 at 15:27
@Cyber The question seems to imply that there's only one such row. And if not, it provides no guidance for how to select the one to use. – Barmar Oct 27 '21 at 15:33
@Cyber What kind of "supporting information" do we supply for simple coding answers? The link doesn't say anything like that. – Barmar Oct 27 '21 at 15:34

score 0 · Answer 2 · answered Oct 28 '21 at 00:46

0

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

answered Oct 28 '21 at 00:46

Barmar

741,623
53
500
612

Thanks for the reply. I'll try this code. – Jon Oct 28 '21 at 00:53

MySQL: Copy values from one column to another column

2 Answers2