CODEWITHSUNDEEP
mysqlsql
Steste2020
Steste2020

Reputation: 13

Change the value of a field in multiple tables

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?

Upvotes: 1

Views: 171

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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;

Upvotes: 2

Related Questions