Swap values from the same column

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

create table test1asselect 'dev1' id_device, 'dev1_name' name_device, '2K12' os, 'PR' env from dual union allselect 'dev2' id_device, 'dev2_name' name_device, '2k3' os, 'PR' env from dual union allselect 'dev3' id_device, 'dev3_name' name_device, '2P4' os, 'PR' env from dual union allselect '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 tgtusing (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 tgtusing (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 test1asselect 'dev1' id_device, 'dev1_name' name_device, '2K12' os, 'PR' env from dual union allselect 'dev2' id_device, 'dev2_name' name_device, '2k3' os, 'PR' env from dual union allselect 'dev3' id_device, 'dev3_name' name_device, '2P4' os, 'PR' env from dual union allselect '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

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

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;