Swap 2 rows in a mysql table

0 like 0 dislike
15 views
Hello!

There is a table, for example, with fields Id (int auto_increment) Name

How to use a sql query to swap the Id of the two lines?

PS have a Good weekend :)
by | 15 views

7 Answers

0 like 0 dislike
UPDATE table SET priority=IF(priority=1, 2, 1) WHERE priority IN (1,2) — swaps the order 1 and 2. order must be unique, otherwise 3 of the request.
by
0 like 0 dislike
And nobody asked, but WHY need it? sys-id and auto-increment that is issued by the sequence generator once and for all. Why do you want to do this?
by
0 like 0 dislike
priorities should be put through a special field in the table and id and that id you want to change it
Just add another field to the table type priority and he puts the desired values, and the program itself, instead of ORDER BY id, place ORDER BY piority and your problem will be solved
by
0 like 0 dislike
SQL does not know how to change from two lines simultaneously, using a third mean value.
by
0 like 0 dislike
ld if you do not have a primary key but the same value does not contain, it should in theory work like this (may need to swap the numbers 0 and 1)
UPDATE table SET ld=IF(ld=0, 1, IF(ld=1, 0, ld));
by
0 like 0 dislike
First change the id of one of the rows to something else, then change id of the second row on the old id of the first row, and then change the first string id to the old id of the second row.
\r
UPDATE table SET ld=9999999 WHERE id = 1;
UPDATE table SET ld=1 WHERE id = 2;
UPDATE table SET ld=2 WHERE id = 9999999;
\r
When this will knock the value of the auto increment for the id but can be handles to change the desired via ALTER TABLE.
\r
Or are you fundamentally the same query? I think not.
by
0 like 0 dislike
A good occasion to discover the transaction in the DBMS.
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
4 answers
0 like 0 dislike
1 answer
asked Jun 1, 2019 by aaalllsss
0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
110,608 questions
257,186 answers
0 comments
28,802 users