CODEWITHSUNDEEP
mysqlsql
Nitrous
Nitrous

Reputation: 80

Swapping MySQL Data between Rows

The goal is to run a MySQL Query that swaps around the Table ID's.

The Table 

ID  TableID     Car
1     1        Ford Mustang
2     1        Ford Focus
3     1        Ford Ranger
4     2        Toyota 4runner
5     2        Toyota celica
6     3        Chevy Camaro
7     4        Cadillac Escalade
8     4        Cadillac CTS
9     6        Dodge Charger
10    6        Dodge Ram
11    6        Dodge Caravan

If I run these queries

UPDATE table SET tableid='2' where tableid='1'
UPDATE table SET tableid='1' where tableid='2'
UPDATE table SET tableid='5' where tableid='6'

So the idea is that I'd like to swap TableID's 1 and 2. However, what would happen is after the first query all of TableID 1 will merge with TableID 2. Then the second query would Update all of TableID 2 (which includes the old TableID 1 and TableID 2) to TableID 1.

The last query will have no issues as there is no conflict. However, how would I write the query to swap the two TableID's in this instance without the tables getting all messed up (joining)?

Upvotes: 4

Views: 411

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Just do it all in one query, using the case statement:

UPDATE table
    SET tableid = (case when tableid = '1' then '2' 
                        when tableid = '2' then '1'
                        when tableid = '5' then '6'
                   end)
    where tableid in ('1', '2', '5');

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20804

I would use a temp table.

insert into my_temptable 
(id, newtableid)
select id
, case when tableid = '2' then '1'
etc
end
from originaltable

Then update the original table from the temp table.

Upvotes: 2

Related Questions