Home » SQL & PL/SQL » SQL & PL/SQL » can we revert value of boolean column in single statement
can we revert value of boolean column in single statement [message #252592] Thu, 19 July 2007 05:38 Go to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Hi.

I have a table in which there is a column with value Y and N.
Now i want to update the table in single stmt so that all Y will convert to N and all N will convert to Y. Is it possible to update with a single sql statement.

Regards,
Prashant
Re: can we revert value of boolean column in single statement [message #252602 is a reply to message #252592] Thu, 19 July 2007 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes. Yes. Yes.

I summarize here the answer to the same question you posted in 3 topics.

Regards
Michel
Re: can we revert value of boolean column in single statement [message #252619 is a reply to message #252592] Thu, 19 July 2007 07:42 Go to previous messageGo to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Michel

sorry for the trouble.... that was due to a misunderstanding ... i tried to delete the extra post but could not found the way.

can u pls tell me the way to perform the above operation!

Regards,
Prashant
Re: can we revert value of boolean column in single statement [message #252623 is a reply to message #252619] Thu, 19 July 2007 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the DECODE function.

Regards
Michel
Re: can we revert value of boolean column in single statement [message #252673 is a reply to message #252592] Thu, 19 July 2007 10:53 Go to previous messageGo to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Thanx Michel... now i m able to do this.
Re: can we revert value of boolean column in single statement [message #252677 is a reply to message #252673] Thu, 19 July 2007 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice!
Now put the solution if someone else has the same issue.


Share what you know, learn what you don't.
Michel
Re: can we revert value of boolean column in single statement [message #252823 is a reply to message #252592] Fri, 20 July 2007 03:01 Go to previous messageGo to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

I have the table emp with column emp_id, emp_name, type

with type is having value C(for contract) and P( for permanent)
now i need to change the type column from P to C and C to P in one go and i used the below query.

UPDATE emp A SET type = (SELECT DECODE(type,'C','P','P','C') FROM emp B WHERE B.EMP_ID=A.EMP_ID)

Thnx
Re: can we revert value of boolean column in single statement [message #252828 is a reply to message #252823] Fri, 20 July 2007 03:11 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just use (adding the last case in DECODE):
UPDATE emp A SET type = DECODE(type,'C','P','P','C',type);

Regards
Michel
Previous Topic: top 50
Next Topic: 12-month range window count
Goto Forum:
  


Current Time: Sun Dec 04 08:35:30 CST 2016

Total time taken to generate the page: 0.07429 seconds