Home » SQL & PL/SQL » SQL & PL/SQL » interchanging values (9.2.0.6.0)
interchanging values [message #356943] Mon, 03 November 2008 05:26 Go to next message
atozvs
Messages: 12
Registered: May 2008
Location: Hyderabad
Junior Member
Hi

below is my table structure

create table temp1(col1 number primary key, col2 number);
insert into temp1 values(1,1);
insert into temp1 values(2,1);
insert into temp1 values(3,1);
insert into temp1 values(4,2);
insert into temp1 values(5,2);
commit;

now i want to update the col2 by changing its value to 2 if its 1 currently and to 1 if its value is 2 currently.

i.e before update

Col1 Col2
-------------------
1 1
2 1
3 1
4 2
5 2

After update it should be.

Col1 Col2
-------------------
1 2
2 2
3 2
4 1
5 1

Can anyone please help me to get a single uodate query for this.

thanks
Re: interchanging values [message #356944 is a reply to message #356943] Mon, 03 November 2008 05:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search for case statement in the oracle reference manual.

Hope this helps.

Regards

Raj
Re: interchanging values [message #356945 is a reply to message #356944] Mon, 03 November 2008 05:43 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
or simple UPDATE with (3-col2)

Re: interchanging values [message #356950 is a reply to message #356945] Mon, 03 November 2008 05:51 Go to previous messageGo to next message
atozvs
Messages: 12
Registered: May 2008
Location: Hyderabad
Junior Member
Hi Raj,

CASE in UPDATE statement????
I am new to this one. can you please throw some light on this.

thanks
Re: interchanging values [message #356953 is a reply to message #356950] Mon, 03 November 2008 05:54 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Have a look at the manual and try to come up with the update statement yourself. By doing it this way you will learn. I don't want to spoonfeed. Hence I am bit reluctant to post the actual code. If you stuck come back to us and we will be able to guide you.

Regards

Raj
Re: interchanging values [message #356956 is a reply to message #356950] Mon, 03 November 2008 05:58 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See update_set_clause syntax:
Quote:
SET column = expression


Regards
Michel
Previous Topic: insert with condition
Next Topic: Creating Date break down result
Goto Forum:
  


Current Time: Sun Dec 11 08:24:16 CST 2016

Total time taken to generate the page: 0.11037 seconds