I haven’t written a Mysql query operation for a long time, but I have an idea. For the records in the above table, p_id and old_id are in one table, and both comparison operations and update operations are required. Suggestion:
Step 1: Split a temporary table based on the main table tmp_id, old_tmp_id (corresponding to old_id in the main table)
tmp_id old_tmp_id
1 30
2 10
3 20
Step 2: left join query, where p_id == old_tmp_id, set p_id = tmp_id
I haven’t written a Mysql query operation for a long time, but I have an idea. For the records in the above table, p_id and old_id are in one table, and both comparison operations and update operations are required. Suggestion:
tmp_id old_tmp_id
1 30
2 10
3 20
表名test
update test t1
inner join test t2 on t1.p_id = t2.old_id
set t1.p_id = t2.id
If you can’t get one SQL statement, just put it in the transaction and update it twice
UPDATE test t1
INNER JOIN test t2 ON t1.p_id = t2.old_id
SET t1.p_id = t2.id