I need to clean up data in two tables. These are my tables:
Table 1
id | name | visible 48871 | John | 0 9711015| Luke | 1 547574 | Mike | 0
Table 2
id | parent_id | visible 1 | 48871 | 0 2 | 9711015 | 1 3 | 547574 | 0
I would like to update the ids of id (first table) and parent_id (second table) increasingly. So I would like to transform the two tables like this:
Table 1
id | name | visible 1 | John | 0 2 | Luke | 1 3 | Mike | 0
Table 2
id | parent_id | visible 1 | 1 | 0 2 | 2 | 1 3 | 3 | 0
It’s possible? What should i use?
Advertisement
Answer
MySQL lets up update multiple tables at the same time. So, you can do:
update table1 t1 join (select t1.*, row_number() over (order by t1.id) as new_id from table1 t1 ) tt1 on t1.id = tt1.id left join table2 t2 on t2.parent_id = t1.id set t1.id = tt1.new_id, t2.parent_id = tt1.new_id;