Swap primary keys between two records in the same table in Postgres

What if we need to swap two primary keys on the same table? What's the best and efficient way to do the same? let's check Now:-

Let's assume we have a user table which has 3 columns among all the rows I have taken two sample rows which are the target rows for us.

Id
Name
Email
101
Sabyasachi
XX@gmail.com
105
Saghosh
xxx@gmail.com

We need to swap the primary keys (which are unique by nature) between those two records so our desired result will be something like  

Id
Name
Email
105
Sabyasachi
XX@gmail.com
101
Saghosh
xxx@gmail.com


Now lets buield a query using CTE(common table expression) to solve the problem:-

with source_user as (
  select
    id
  from
    users
  where
    id = 101
),
destination_user as (
  select
    id
  from
    users
  where
    id = 105
)
update users
set id =
  case 
    when users.id = source_user.id then destination_user.id
    when users.id = destination_user.id then source_user.id
   end
from source_user, destination_user   
where users.id IN (source_user.id, destination_user.id);

That's it will do the work for us. Easy but tricky solution  :) 

Comments

Post a Comment

Popular posts from this blog

Debug Nodejs inside docker container

How to add a bootstrap table with fixed header and scrollable body