swap [message #435279] |
Tue, 15 December 2009 07:25  |
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   |
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   |
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 #435293 is a reply to message #435287] |
Tue, 15 December 2009 07:53   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
koduru.a wrote on Tue, 15 December 2009 13:37NO 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 #435296 is a reply to message #435293] |
Tue, 15 December 2009 08:03   |
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   |
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  |
 |
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
|
|
|