CODEWITHSUNDEEP
sqlsql-servert-sql
Subpar Web Dev
Subpar Web Dev

Reputation: 3260

How can I safely swap the ids of two rows that are references in other tables?

So let's say I have a table like

   Table 1 
=============
id | ... 
=============
 1 | ... 
 2 | ... 
 3 | ...
 .    .
 .    .
 .    .

       Table 2 
=======================
id | table1_id | ... 
=======================
 1 |     1     | ... 
 2 |     1     | ... 
 3 |     2     | ... 
 .       .        .
 .       .        . 
 .       .        .

where table1_id in Table 2 references id in Table 1.

I want to the ids of the rows with id=1 and id=2.

Can I do this without screwing up the relationships?

Upvotes: 0

Views: 68

Answers (3)

Anish Gopi
Anish Gopi

Reputation: 66

Insert a dummy entry with copy of id 1 row in [Table 1] with new id. Then update all columns of id 1 with id 2 with following query

UPDATE  T
SET T.col2  = S.col2
    ,T.col3 = S.col3
    ,T.col4 = S.col4
    .     = .
    .     = .
    .     = .
[Table 1] T
    CROSS JOIN ( SELECT col2
            ,col3
            ,col4
            .
            .
         FROM [Table 1]
         WHERE id = 2
        ) S
WHERE id = 1

In this same way update all columns of row id 2 with newly created row. After this delete newly created row.

Upvotes: 3

osmanraifgunes
osmanraifgunes

Reputation: 1468

Add a dummy row to table1.

Update table2 : update table2 set table1-id = dummyid where table1-id = 1 .

Then: update table2 set table1-id = 1 where table1-id = 2.

Lastly: update table2 set table1-id = 2 where table1-id = dummyid.

You can use this logic to change table1 also.

Upvotes: 0

Stefan Dorner
Stefan Dorner

Reputation: 171

I would create a temporary entry with a new id in Table 1, move the references in Table 2 from table1_id=1 to the new id, move table1_id=2 to table_1_id=1 and then move the temporary referencves to table1_id=2.

Upvotes: 0

Related Questions