Consider a table test contains an attributes of id and name.

The values for that.

Id   |   Name
1    |   Raj
2    |   Kumar

From the above sample table, I know only the id, So through the Id I need to swap the name, like given below,

Id   |   Name
1    |   Kumar
2    |   Raj
  • 2
    Is this in a query? Or do you want to perform an update? – Colin 't Hart Mar 3 '16 at 8:01
  • What ever, but it need to swap. – Ganapathy Mar 3 '16 at 9:39
update the_table
   set name = case id
                 when 1 then (select name from the_table where id = 2)
                 when 2 then (select name from the_table where id = 1)
where id in (1,2);

This assumes that id is unique (e.g. the primary key)

| improve this answer | |
  • Nice answer.... – Ganapathy Mar 3 '16 at 9:40
  • Can you explain your query line by line, How its work.... – Ganapathy Mar 10 '16 at 12:08

One more way, which can be modified easily to do more complicated changes (e.g. swap multiple couples or move values from 1->2->3->1, update multiple columns, etc.):

with changes (update_id, take_value_from_id) as
    ( values
        (1, 2),    -- update row with id=1 from row with id=2
        (2, 1)     -- update row with id=2 from row with id=1
  data as
    ( select c.update_id, val.name
      from changes as c
        join the_table as val  on val.id = c.take_value_from_id
update the_table as upd
set name = d.name 
from data as d  
where upd.id = d.update_id ;
| improve this answer | |

Why not just swap the IDs instead of the value(s)? Or do the other columns have to remain as they were?

Anyway, in case someone else reads this question and is looking for this, it's just:

select case id when 1 then 2 when 2 then 1 else id end as id, ...other columns... from ...table...;

or as an update:

update ...table... set id = (case id when 1 then 2 when 2 then 1 else id end);

(I can never remember if the else id clause is needed.)

| improve this answer | |
  • 1
    The else id is needed unless you have a where id in (1,2) which is good to have anyway. No need to update the whole table when only 2 rows need updating. – ypercubeᵀᴹ Mar 3 '16 at 16:36
  • Your query is okay, it's work. But I need to swap the name not a id. Because if you swap id, Then the id is not in order. – Ganapathy Mar 10 '16 at 12:40
  • 1
    Huh? Data in a table or in query results are never in any particular order. And if you are relying on a particular ordering because of the way a query works, or the way in which the data happens to be stored, your queries are broken. To achieve a particular ordering, you need to use order by. – Colin 't Hart Mar 10 '16 at 15:04
  • 1
    Update doesn't work in postgres as each set is checked against constraints individually – Pedro Borges Mar 16 at 19:41
  • @PedroBorges You're right, thanks. Needs deferred constraints to work: dbfiddle.uk/… – Colin 't Hart Mar 16 at 19:48

We can swap two values from a particular row by using the 'With Query'. Using 'with' we can take the backup and use it.
The query to solve the requirement,

with backup1 as (select name from test where id = 2), 
     backup2 as (update test set name = (select name from test where id = 1) where id = 2)
update test set name = (select * from backup1) where id = 1;

In the above query, the backup1 contains the name of whose id is 2, After that using update query I took the name of whose id is 1 and
stored in the name field where id is 2.
Then again using the update query I took the name from the backup and stored in the name field where id is 1, Like this we can swap.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.