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.
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 :)
nice and great post on Ruby on Rails online course
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteTÜL PERDE MODELLERİ
ReplyDeleteNumara Onay
mobil odeme bozdurma
Nft nasil alinir
Ankara evden eve nakliyat
TRAFİK SİGORTASİ
dedektör
WEB SİTESİ KURMA
ask romanlari
Good content. You write beautiful things.
ReplyDeletesportsbet
hacklink
mrbahis
vbet
vbet
sportsbet
taksi
mrbahis
korsan taksi
This comment has been removed by a blog administrator.
ReplyDeletehttps://saglamproxy.com
ReplyDeletemetin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
8E11
balıkesir
ReplyDeletekarabük
niğde
samsun
üsküdar
YUTKZ
web postegro
ReplyDeletetakipcimx 1000
techy hit tools
J4V4T