Swap primary keys in mysql

Question

I am trying to achieve the same thing as asked in this question sql swap primary key values but I can't seem to get it running with newer mysql versions becausue of the error message Error Code: 1706. Primary key/partition key update is not allowed since the table is updated both as 't' and 't2'.

My table would look like this

id   name
---------
1    john

id   name
---------
2    jim

and expected result would look like this

id   name
---------
2    john

id   name
---------
1    jim

Is there any "one-liner" for that?

Please show some sample data and expected output on what you are trying to do ? — Madhur Bhaiya, Nov 14 '18 at 09:14
done, I copied the example from the answer to the linked question — jPO, Nov 14 '18 at 09:16
Did you try the other answers in the linked question ? Like this:https://stackoverflow.com/a/26584576/2469308 — Madhur Bhaiya, Nov 14 '18 at 09:55
I am not even trying because the approach is the same and the error message would be that one of the values is a duplicate. Approach with decode would deliver a message that the parameter count is incorrect. — jPO, Nov 14 '18 at 09:56

Snowy Heart · Answer 1 · 2018-11-14T09:55:20.050

0

I believe that the easier and safer way to do it is to leave the primary key as it is and swap the values of the other columns, should give you the same results

edited Nov 14 '18 at 09:55

answered Nov 14 '18 at 09:52

Snowy Heart

1
2

1

It does not work in newer versions of MySQL. check the comment: https://stackoverflow.com/questions/2810606/sql-swap-primary-key-values#comment15792689_2810789 and that is the whole point of this question. – Madhur Bhaiya Nov 14 '18 at 09:53
This is not the case. Welcome to the community. If that was the case the error message would state something else not the one I am getting – jPO Nov 14 '18 at 09:54
Okay^^ I'll change my answer – Snowy Heart Nov 14 '18 at 09:54
This would work for own tables where I got control over. As the tables I am working with do not belong to me I cannot be sure, that the number of the columns doesn't change. I also would need to count on the fact that there are no other uniques or primaries... – jPO Nov 14 '18 at 10:02

Swap primary keys in mysql

1 Answers1