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).

Answer 1

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

Answer 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