CODEWITHSUNDEEP
mysqlsqlsql-update
Phil LA
Phil LA

Reputation: 163

Multi Insert within same table

It might be a retarded question but even after 2 hours of searching I just cant find any good way to deal with that..

I am trying to switch 2 rows within one table...

i broke the structure down to that:

ID time 
 3  100
 5  200

the rows do have more columns in the actual code but i only need to swap those two values..

I tried to achieve this with two Update statements .. But in the end i ended up with 4 sql statements and connections therefore :/ .. I had to change every value in a single sql to make 100% sure they swap the right way. But it seem pretty bad code

If i swap the values with 2 update sqls the second one fails because there is no unique matche in the table anymore ...

I googled for hours to find a way to achieve this with one sql and one connection but i failed.

so at the moment i am doing: 

$query = "UPDATE `" . $table . "` SET...; 
$db->insert($query);

$query2 = "UPDATE `" . $table . "` SET...;
$db->insert($query2);

... two more times

Upvotes: 0

Views: 41

Answers (2)

Strawberry
Strawberry

Reputation: 33935

E.g.

UPDATE my_table x 
  JOIN my_table y 
    ON x.id IN(3,5)
   AND y.id IN(3,5) 
   AND x.id <> y.id 
   SET x.time = y.time;

Upvotes: 1

Dmitry Eresov
Dmitry Eresov

Reputation: 106

Use a placeholder ID value (I am using 99999 in this example). This reduces code to 3 simple sql statements.

update table set ID = 99999 where id = 3;
update table set ID = 3 where ID = 5;
update table set ID = 5 where ID = 99999;

Upvotes: 0

Related Questions