Swap primary keys in mysql

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?

edited Nov 14 '18 at 9:14

asked Nov 14 '18 at 9:08

jPO

1,1581017

2

update your question and show the related sql code

– scaisEdge
Nov 14 '18 at 9:11

Please show some sample data and expected output on what you are trying to do ?

– Madhur Bhaiya
Nov 14 '18 at 9:14

done, I copied the example from the answer to the linked question

– jPO
Nov 14 '18 at 9:16

Did you try the other answers in the linked question ? Like this:stackoverflow.com/a/26584576/2469308

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:56

|
show 2 more comments

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?

edited Nov 14 '18 at 9:14

asked Nov 14 '18 at 9:08

jPO

1,1581017

2

update your question and show the related sql code

– scaisEdge
Nov 14 '18 at 9:11

Please show some sample data and expected output on what you are trying to do ?

– Madhur Bhaiya
Nov 14 '18 at 9:14

done, I copied the example from the answer to the linked question

– jPO
Nov 14 '18 at 9:16

Did you try the other answers in the linked question ? Like this:stackoverflow.com/a/26584576/2469308

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:56

|
show 2 more comments

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?

edited Nov 14 '18 at 9:14

asked Nov 14 '18 at 9:08

jPO

1,1581017

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?

mysql primary-key swap

edited Nov 14 '18 at 9:14

asked Nov 14 '18 at 9:08

jPO

1,1581017

edited Nov 14 '18 at 9:14

asked Nov 14 '18 at 9:08

jPO

1,1581017

edited Nov 14 '18 at 9:14

asked Nov 14 '18 at 9:08

jPO

1,1581017

asked Nov 14 '18 at 9:08

jPO

1,1581017

asked Nov 14 '18 at 9:08

jPO

1,1581017

2

update your question and show the related sql code

– scaisEdge
Nov 14 '18 at 9:11

Please show some sample data and expected output on what you are trying to do ?

– Madhur Bhaiya
Nov 14 '18 at 9:14

done, I copied the example from the answer to the linked question

– jPO
Nov 14 '18 at 9:16

Did you try the other answers in the linked question ? Like this:stackoverflow.com/a/26584576/2469308

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:56

|
show 2 more comments

2

update your question and show the related sql code

– scaisEdge
Nov 14 '18 at 9:11

Please show some sample data and expected output on what you are trying to do ?

– Madhur Bhaiya
Nov 14 '18 at 9:14

done, I copied the example from the answer to the linked question

– jPO
Nov 14 '18 at 9:16

Did you try the other answers in the linked question ? Like this:stackoverflow.com/a/26584576/2469308

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:56

update your question and show the related sql code

– scaisEdge
Nov 14 '18 at 9:11

Please show some sample data and expected output on what you are trying to do ?

– Madhur Bhaiya
Nov 14 '18 at 9:14

done, I copied the example from the answer to the linked question

– jPO
Nov 14 '18 at 9:16

Did you try the other answers in the linked question ? Like this:stackoverflow.com/a/26584576/2469308

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:56

|
show 2 more comments

1 Answer
1

active

oldest

votes

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 9:55

answered Nov 14 '18 at 9:52

Snowy Heart

1

It does not work in newer versions of MySQL. check the comment: stackoverflow.com/questions/2810606/… and that is the whole point of this question.

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:54

Okay^^ I'll change my answer

– Snowy Heart
Nov 14 '18 at 9: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

add a comment |

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53296477%2fswap-primary-keys-in-mysql%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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 9:55

answered Nov 14 '18 at 9:52

Snowy Heart

1

It does not work in newer versions of MySQL. check the comment: stackoverflow.com/questions/2810606/… and that is the whole point of this question.

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:54

Okay^^ I'll change my answer

– Snowy Heart
Nov 14 '18 at 9: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

add a comment |

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 9:55

answered Nov 14 '18 at 9:52

Snowy Heart

1

It does not work in newer versions of MySQL. check the comment: stackoverflow.com/questions/2810606/… and that is the whole point of this question.

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:54

Okay^^ I'll change my answer

– Snowy Heart
Nov 14 '18 at 9: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

add a comment |

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 9:55

answered Nov 14 '18 at 9:52

Snowy Heart

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 9:55

answered Nov 14 '18 at 9:52

Snowy Heart

edited Nov 14 '18 at 9:55

answered Nov 14 '18 at 9:52

Snowy Heart

answered Nov 14 '18 at 9:52

Snowy Heart

answered Nov 14 '18 at 9:52

Snowy Heart

1

It does not work in newer versions of MySQL. check the comment: stackoverflow.com/questions/2810606/… and that is the whole point of this question.

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:54

Okay^^ I'll change my answer

– Snowy Heart
Nov 14 '18 at 9: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

add a comment |

1

It does not work in newer versions of MySQL. check the comment: stackoverflow.com/questions/2810606/… and that is the whole point of this question.

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:54

Okay^^ I'll change my answer

– Snowy Heart
Nov 14 '18 at 9: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

It does not work in newer versions of MySQL. check the comment: stackoverflow.com/questions/2810606/… and that is the whole point of this question.

– Madhur Bhaiya
Nov 14 '18 at 9: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 9:54

Okay^^ I'll change my answer

– Snowy Heart
Nov 14 '18 at 9: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

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Pfthb