All geek questions in one place

MySQL data exchange between rows

The goal is to run a MySQL query that exchanges table identifiers.

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 would like to exchange TableID 1 and 2. However, what happens, after the first query, all TableID 1 will merge with TableID 2. Then the second query will update all TableID 2 (which includes the old TableID 1 and TableID 2 ) in TableID 1.

There will be no problems in the last query, as there are no conflicts. However, how would I write a query to replace two table identifiers in this instance, without the tables being messed up (joined)?

+4
source share
2 answers

Just do everything in one request using the case :

 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');
+3
source

I would use a temporary table.

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

Then update the source table from temp table.

+2
source

Source: https://habr.com/ru/post/1494599/

More articles:


All Articles