Swap 2 rows in mysql table

Question

PHP

Hello!

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

How to swap Ids for two rows using sql query?

PS Have a nice weekend :)

Answer 1

UPDATE table SET priority=IF(priority=1, 2, 1) WHERE priority IN (1,2) - swaps order 1 and 2. order must not be unique, otherwise 3 requests.

Answer 2

And no one asked - WHY is this necessary? sys-id is for that and auto-increment, which is issued by the sequence generator once and for all. Why do you want to do this?

Answer 3

priorities must be set using a special field in the table, and id or even id so that you do not need to change it.
Just add one more field to the priority type table and set the necessary values ​​for it, and in your program, instead of ORDER BY id, set ORDER BY piority and your problem will be solved

Answer 4

SQL is not able to change two rows at the same time, meaning through the third value.

Answer 5

if your ld is not a primary key, but does not contain identical values, then it should work like this (let's swap the numbers 0 and 1)
UPDATE table SET ld=IF(ld=0, 1, IF(ld=1 , 0, ld));

Answer 6

A good reason to discover transactions in the DBMS.

Answer 7

Ok, I figured it out, now I'll ask it differently.
There is a table, the priority field is placed in a separate column. You want to change the priority of records A and B. Namely, swap, swap. The question is why? What is the business sense in such a change of priority?

Answer 8

Hm. Those. do you want to essentially store the code in the db? line of code = row in table in database? And do you need priorities so that you can then select these lines with a request so that they form the correct piece of code?

Answer 9

Let's say we have a table of participants in matches participants with fields (id, match_id, position).
It is necessary to change the places of the participants in the match.
For clarity, let's add the name field to it, which stores the original match_id-position combination.
Uniqueness is set by the match_id+position fields.
Request:

INSERT INTO participants (`id`, `position`, match_id) 
VALUES (4, 0, 2), (3, 2, 2), (4, 1, 2) 
ON DUPLICATE KEY UPDATE `position` = VALUES(`position`)

Result:
Works without removing uniqueness, with one request!