Home » SQL & PL/SQL » SQL & PL/SQL » Swap 2 variables without using a temperory variable
Swap 2 variables without using a temperory variable [message #255342] Tue, 31 July 2007 08:52 Go to next message
lijojoseph
Messages: 9
Registered: July 2007
Location: Mumbai
Junior Member
There are 2 variables called x and y. Is it posssible to swap the values from x to y and y to x with out using dummy variables and it can be done only by using a single statement ?.

I know that the variables can be swapped without using a temperory variable by the following method

x =x+y;
y =x-y;
x =x-y;
But is it possible to swap it in a single statement?
Re: Swap 2 variables without using a temperory variable [message #255346 is a reply to message #255342] Tue, 31 July 2007 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
But it can be done in SQL if data are in a table.

Regards
Michel
Re: Swap 2 variables without using a temperory variable [message #255347 is a reply to message #255346] Tue, 31 July 2007 08:59 Go to previous messageGo to next message
lijojoseph
Messages: 9
Registered: July 2007
Location: Mumbai
Junior Member
Michel,

Thanks.
Can you please explain the same?

Thanks
Lijo
Re: Swap 2 variables without using a temperory variable [message #255349 is a reply to message #255342] Tue, 31 July 2007 08:59 Go to previous messageGo to next message
balassvasan
Messages: 3
Registered: July 2007
Junior Member
select x, y into y, x from dual;
Re: Swap 2 variables without using a temperory variable [message #255352 is a reply to message #255349] Tue, 31 July 2007 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2    x integer := 1;
  3    y integer := 2;
  4  begin
  5    select x, y into y, x from dual;
  6    dbms_output.put_line('x='||x||' y='||y);
  7  end;
  8  /
x=2 y=1

PL/SQL procedure successfully completed.

SQL> create table t (x integer, y integer);

Table created.

SQL> insert into t values (1,2);

1 row created.

SQL> select * from t;
         X          Y
---------- ----------
         1          2

1 row selected.

SQL> update t set x=y, y=x;

1 row updated.

SQL> select * from t;
         X          Y
---------- ----------
         2          1

1 row selected.

Regards
Michel
Re: Swap 2 variables without using a temperory variable [message #255354 is a reply to message #255349] Tue, 31 July 2007 09:09 Go to previous messageGo to next message
lijojoseph
Messages: 9
Registered: July 2007
Location: Mumbai
Junior Member
Thanks bala
Re: Swap 2 variables without using a temperory variable [message #255355 is a reply to message #255342] Tue, 31 July 2007 09:10 Go to previous message
lijojoseph
Messages: 9
Registered: July 2007
Location: Mumbai
Junior Member
Thanks Michel
Previous Topic: Concatenation problem in stored procedure (merged by LF)
Next Topic: Question on duplicates
Goto Forum:
  


Current Time: Sun Dec 04 06:38:16 CST 2016

Total time taken to generate the page: 0.06978 seconds