Home » SQL & PL/SQL » SQL & PL/SQL » swap
swap [message #435279] Tue, 15 December 2009 07:25 Go to next message
koduru.a
Messages: 3
Registered: December 2009
Location: Chennai
Junior Member

Dear Members,

My Query is..

I am having the table like this..

empno Sal
-----------------
1111 10000
2222 20000

I want to swap the sal like..
empno sal
----------------
1111 20000
2222 10000

pls guide me..

Thanks,
Amar.
Re: swap [message #435281 is a reply to message #435279] Tue, 15 December 2009 07:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you want to happen if there are more than two rows in the table?
Re: swap [message #435282 is a reply to message #435279] Tue, 15 December 2009 07:28 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Does the table have only 2 rows? Does that table have many rows but only the two listed rows should be changed? Is there some hidden factor (by any chance) that you are not telling us?
Re: swap [message #435284 is a reply to message #435282] Tue, 15 December 2009 07:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Such as when the homework is due by?
Re: swap [message #435285 is a reply to message #435284] Tue, 15 December 2009 07:35 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Such as when the homework is due by?

Laughing
Re: swap [message #435287 is a reply to message #435282] Tue, 15 December 2009 07:37 Go to previous messageGo to next message
koduru.a
Messages: 3
Registered: December 2009
Location: Chennai
Junior Member

NO the table having only two rows and two columns but we need to swap with in a single select statment.
Re: swap [message #435291 is a reply to message #435287] Tue, 15 December 2009 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select empno sal, sal empno from emp;

Regards
Michel
Re: swap [message #435293 is a reply to message #435287] Tue, 15 December 2009 07:53 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
koduru.a wrote on Tue, 15 December 2009 13:37
NO the table having only two rows and two columns but we need to swap with in a single select statment.

and absolutely no other requirements or restrictions. Purely that the required output:
empno sal
----------------
1111 20000
2222 10000

is done in a single sql statement
Re: swap [message #435295 is a reply to message #435291] Tue, 15 December 2009 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Ooops! I did not swap the correct stuff.

Regards
Michel
Re: swap [message #435296 is a reply to message #435293] Tue, 15 December 2009 08:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
select e1.empno
      ,e2.sal
from   emp e1
      ,emp e2
where  e1.empno != e2.empno 
Re: swap [message #435298 is a reply to message #435296] Tue, 15 December 2009 08:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or we could do one of these two:
with emp as (select 1111 empno, 10000 sal from dual union all
             select 2222 empno, 20000 sal from dual )
select empno
       ,nvl(lead(sal) over (order by empno)
           ,lag(sal) over (order by empno))
from emp;

or this
with emp as (select 1111 empno, 10000 sal from dual union all
             select 2222 empno, 20000 sal from dual )
select empno
      ,last_value(sal) over (order by empno desc)
from emp;
Re: swap [message #435301 is a reply to message #435296] Tue, 15 December 2009 08:12 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
with 
  emp as (select 1111 empno, 10000 sal from dual union all
             select 2222 empno, 20000 sal from dual ),
  lines as (select level line from dual connect by level <= 2),
  data as (select min(empno) emp1, max(empno) emp2, min(sal) sal1, max(sal) sal2 from emp)
select decode(line, 1, emp1, emp2) empno,
       decode(line, 1, sal2, sal1) sal
from data, lines
/

Regards
Michel

[Updated on: Tue, 15 December 2009 08:13]

Report message to a moderator

Previous Topic: Alphanumeric
Next Topic: Jobs running twice
Goto Forum:
  


Current Time: Sat Feb 08 20:31:24 CST 2025