Swap values from the same column

Question

My table looks like this :

id_device    name_device     os     env

dev1         dev1_name       2K12   PR
dev2         dev2_name       2k3    PR

what i want to achieve is to swap the id and the name of the device :

id_device    name_device     os     env

dev2         dev2_name       2K12   PR
dev1         dev1_name       2k3    PR

Is it possible to do that in one query ? will we need a temp variable ? EDIT : dev1 and dev2 are going to pe set by the user (via a php formular).

thousands...the update will be triggered by a php event. and on every query there will be one swap only. — RidRoid, Aug 28 '15 at 12:48
I don't see how these two records even relate so in a table of thousands, how would we know what two to swap between? Couldn't the OS between the two just be swapped or are there other fields? — xQbert, Aug 28 '15 at 12:52
Do you have a formula to choose records whose id and names will be replaced with each others? — Abdullah Nehir, Aug 28 '15 at 12:54
Assuming you want to do this any time you update the id and name a trigger is probably the cleanest way to do this. — John Kuhns, Aug 28 '15 at 12:55
there are too many fields, i just gave 4 on the example to show you that i only want the name and the id to swap. the dev1 and dev2 are going to be provided by the user. — RidRoid, Aug 28 '15 at 12:55
@AbdullahNehir yes. the user will provide the id's to swap just updated my question btw :) — RidRoid, Aug 28 '15 at 12:58
@RidRoid Good. Unfortunately I don't know any other way except declaring temporary swap variables. It's not hard though, I can provide a sample script. — Abdullah Nehir, Aug 28 '15 at 13:03
So id_device is a unique key in the table? And is this swapping something that occurs regularly or are you only now correcting data errors? — Thorsten Kettner, Aug 28 '15 at 13:06
@ThorstenKettner yes id_device is a unique key. the swapping will indeed occur regularly, it's a part of a swapping process. — RidRoid, Aug 28 '15 at 13:12
Okay, I Image problems if it is used as a foreign key in some child table. However, I would probably simply split the table in two, so as to only have to change the referencing id to the other table. — Thorsten Kettner, Aug 28 '15 at 13:42

Juan Carlos Oropeza · Answer 1 · 2015-08-28T16:11:57.377

2

With a single query you can create the swap data in a internal CTE.

Full query:

SQL Fiddle Demo

update test1 t set 
(name_device, id_device) = (select s.target_name, s.target_id
                            from  (SELECT 
                                        t1.id_device source_id, 
                                        t2.name_device target_name,
                                        t2.id_device target_id
                                   FROM 
                                       test1 t1 inner join 
                                       test1 t2 on t1.id_device <> t2.id_device
                                   WHERE 
                                       t1.id_device in ('dev1', 'dev2')
                                   and t2.id_device in ('dev1', 'dev2')) s
                            where s.source_id = t.id_device
                            )
where id_device in ('dev1', 'dev2');

I have to update my query because oracle doesn't accept update for a cte

edited Aug 28 '15 at 16:11

answered Aug 28 '15 at 13:04

Juan Carlos Oropeza

47,252
12
78
118

@Aramillo I was 100% sure you can. Now you make me 95%. Still in sql server you have this [**Sample**](http://stackoverflow.com/questions/11562536/update-records-in-table-from-cte) – Juan Carlos Oropeza Aug 28 '15 at 13:25
GG, maybe in sqlserver, but in oracle you need to do something like [this](http://stackoverflow.com/questions/5380559/update-statement-using-with-clause) – Aramillo Aug 28 '15 at 13:27
@JuanCarlosOropeza the query throws an error : 00928. 00000 - "missing SELECT keyword" line 15. – RidRoid Aug 28 '15 at 13:47
@Aramillo I update my query as your suggestion. Is working ok in the oracle fiddle now. Thanks – Juan Carlos Oropeza Aug 28 '15 at 14:26
You could reduce the number of calls to the tables by converting the UPDATE into a MERGE statement, so 3 calls to the table (1 for the update, 2 for the query to get the data) rather than 5. Or you could do the update of both columns in one go, rather than idividually (as in `set (id_device, name_device) = (select target_id, target_name from (...))`). Also, you're missing the where clause off your update statement to restrict the update to the two rows in question. SQLFiddle: http://sqlfiddle.com/#!4/a29e4/6 – Boneist Aug 28 '15 at 16:00
Yes, I saw your answer. didn't know about merge when wrote this answer. Regarding dual set. Is that a oracle thing or other rdbms support that? I will fix the where clause. Thanks I learn two things today – Juan Carlos Oropeza Aug 28 '15 at 16:07
I don't know about other rdbm's, sorry. I'd be surprised if they didn't support it though! (But then again, I'm constantly surprised these days! *{;-) ) it's good to learn new things; that's why I like helping out on forums; you get to learn loads of new things! – Boneist Aug 28 '15 at 18:10

Boneist · Answer 2 · 2015-08-28T15:42:03.427

Here's an alternative, using MERGE. It assumes that id_device is unique, though.

create table test1
as
select 'dev1' id_device, 'dev1_name' name_device, '2K12' os, 'PR' env from dual union all
select 'dev2' id_device, 'dev2_name' name_device, '2k3' os, 'PR' env from dual union all
select 'dev3' id_device, 'dev3_name' name_device, '2P4' os, 'PR' env from dual union all
select 'dev4' id_device, 'dev4_name' name_device, '2Q7' os, 'PR' env from dual;

select * from test1;

ID_DEVICE NAME_DEVICE OS   ENV
--------- ----------- ---- ---
dev1      dev1_name   2K12 PR 
dev2      dev2_name   2k3  PR 
dev3      dev3_name   2P4  PR 
dev4      dev4_name   2Q7  PR 

merge into test1 tgt
using (select rowid ri,
              id_device old_id_device,
              case when id_device = 'dev1' then lead(id_device) over (order by case when id_device = 'dev1' then 1 when id_device = 'dev2' then 2 end)
                   when id_device = 'dev2' then lag(id_device) over (order by case when id_device = 'dev1' then 1 when id_device = 'dev2' then 2 end)
              end new_id_device,
              name_device old_name_device,
              case when id_device = 'dev1' then lead(name_device) over (order by case when id_device = 'dev1' then 1 when id_device = 'dev2' then 2 end)
                   when id_device = 'dev2' then lag(name_device) over (order by case when id_device = 'dev1' then 1 when id_device = 'dev2' then 2 end)
              end new_name_device,
              os,
              env
       from   test1 t1
       where  t1.id_device in ('dev1', 'dev2')) src
  on (tgt.rowid = src.ri)
when matched then
  update set tgt.id_device = src.new_id_device,
             tgt.name_device = src.new_name_device;

commit;

select * from test1;

ID_DEVICE NAME_DEVICE OS   ENV
--------- ----------- ---- ---
dev2      dev2_name   2K12 PR 
dev1      dev1_name   2k3  PR 
dev3      dev3_name   2P4  PR 
dev4      dev4_name   2Q7  PR

Obviously, for your case where dev1 and dev2 are parameters, your MERGE would look something like:

merge into test1 tgt
using (select rowid ri,
              id_device old_id_device,
              case when id_device = p_device_id1 then lead(id_device) over (order by case when id_device = p_device_id1 then 1 when id_device = p_device_id2 then 2 end)
                   when id_device = p_device_id2 then lag(id_device) over (order by case when id_device = p_device_id1 then 1 when id_device = p_device_id2 then 2 end)
              end new_id_device,
              name_device old_name_device,
              case when id_device = p_device_id1 then lead(name_device) over (order by case when id_device = p_device_id1 then 1 when id_device = p_device_id2 then 2 end)
                   when id_device = p_device_id2 then lag(name_device) over (order by case when id_device = p_device_id1 then 1 when id_device = p_device_id2 then 2 end)
              end new_name_device,
              os,
              env
       from   test1 t1
       where  t1.id_device in (p_device_id1, p_device_id2)) src
  on (tgt.rowid = src.ri)
when matched then
  update set tgt.id_device = src.new_id_device,
             tgt.name_device = src.new_name_device;

where p_device_id1 and p_device_id2 are the parameters for the two device ids being swapped.

-----------------

As an addition to my comment to @goliardico's answer, here's the testcase:

create table test1
as
select 'dev1' id_device, 'dev1_name' name_device, '2K12' os, 'PR' env from dual union all
select 'dev2' id_device, 'dev2_name' name_device, '2k3' os, 'PR' env from dual union all
select 'dev3' id_device, 'dev3_name' name_device, '2P4' os, 'PR' env from dual union all
select 'dev4' id_device, 'dev4_name' name_device, '2Q7' os, 'PR' env from dual;

DECLARE
  device1    varchar2(50);
  device2    varchar2(50);
  dev_rec1   test1%rowtype;
  dev_rec2   test1%rowtype;
BEGIN
  device1 := 'dev1';
  device2 := 'dev2';

  select * INTO dev_rec1 from test1 where id_device = device1;
  select * INTO dev_rec2 from test1 where id_device = device2;

  update test1
  set    id_device = case when id_device = dev_rec1.id_device then dev_rec2.id_device
                          when id_device = dev_rec2.id_device then dev_rec1.id_device
                     end,
         name_device = case when id_device = dev_rec1.id_device then dev_rec2.name_device
                            when id_device = dev_rec2.id_device then dev_rec1.name_device
                       end
  where  id_device in (dev_rec1.id_device, dev_rec2.id_device);

  commit;
END;
/

select * from test1;

ID_DEVICE NAME_DEVICE OS   ENV
--------- ----------- ---- ---
dev2      dev2_name   2K12 PR 
dev1      dev1_name   2k3  PR 
dev3      dev3_name   2P4  PR 
dev4      dev4_name   2Q7  PR

score 1 · Answer 3 · answered Aug 28 '15 at 13:08

Not a single query but PL/SQL. You could make a a procedure with parameters for many records:

DECLARE
  device1    varchar2(50);
  device2    varchar2(50);
  dev_rec1   devs_tablename%rowtype;
  dev_rec2   devs_tablename%rowtype;
BEGIN
  device1 := 'dev1';
  device2 := 'dev2';

  select * INTO dev_rec1 from devs_tablename where id_device = device1;
  select * INTO dev_rec2 from devs_tablename where id_device = device2;
  update devs_tablename set id_device = dev_rec2.id_device || 'TMP',
     name_device = dev_rec2.name_device where id_device = device1;
  update devs_tablename set id_device = dev_rec1.id_device,
    name_device = dev_rec1.name_device where id_device = device2;
  update devs_tablename set id_device = dev_rec2.id_device 
    where id_device = device2 || 'TMP';
END;

they are both updated: set **id_device** = dev_rec2.id_device, **name_device** = dev_rec2.name_device — goliardico, Aug 28 '15 at 14:15
You could do this in a single update statement by using a case statement. I've updated my answer below with the suggested single update statement, as it would be difficult to add it as a comment and still expect it to be readable! — Boneist, Aug 28 '15 at 15:42

Swap values from the same column

3 Answers3

-----------------