Home » SQL & PL/SQL » SQL & PL/SQL » Combining update statement.
Combining update statement. [message #301011] Tue, 19 February 2008 00:18 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi all,

Can anyone help me to combine this following 3 update statement into a single update statement.

 	UPDATE cad b
 	SET    b.exception_id =(SELECT 'CI02XH01'
                            FROM   visualcactus.zipcodes a
                            WHERE  a.zipcode != b.zipcode)
 	WHERE  b.source_system = 'C1X90ZB8N0';
 	

 	UPDATE cad b
 	SET    b.exception_id =(SELECT 'CI02XH02'
 	                        FROM   zipcode a
 	                        WHERE  a.state != b.state)
 	WHERE  b.source_system = 'C1X90ZB8N0';
 	

 	UPDATE cad b
 	SET    b.exception_id =(SELECT 'CI02XH03'
 	                        FROM   zipcode a
 	                        WHERE  a.country != b.country)
 	WHERE  b.source_system = 'C1X90ZB8N0';


Thanks in advance.
fortunethiyagu
Re: Combining update statement. [message #301016 is a reply to message #301011] Tue, 19 February 2008 00:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think these updates would fail, because the inner query would return more than one row.
Besides that, you have to decide which of the three prevails.
Re: Combining update statement. [message #301017 is a reply to message #301011] Tue, 19 February 2008 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use COALESCE or the like depending on your need.
But are you sure each query works and you don't get an error: "ORA-01427: single-row subquery returns more than one row"?

Regards
Michel

[Edit: Frank beat me]

[Updated on: Tue, 19 February 2008 00:27]

Report message to a moderator

Re: Combining update statement. [message #301027 is a reply to message #301017] Tue, 19 February 2008 00:38 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

 	UPDATE cad b
 	SET    b.exception_id IN(SELECT 'CI02XH01'
                            FROM   visualcactus.zipcodes a
                            WHERE  a.zipcode != b.zipcode)
 	WHERE  b.source_system = 'C1X90ZB8N0';
 	

 	UPDATE cad b
 	SET    b.exception_id IN(SELECT 'CI02XH02'
 	                        FROM   zipcode a
 	                        WHERE  a.state != b.state)
 	WHERE  b.source_system = 'C1X90ZB8N0';
 	

 	UPDATE cad b
 	SET    b.exception_id IN(SELECT 'CI02XH03'
 	                        FROM   zipcode a
 	                        WHERE  a.country != b.country)
 	WHERE  b.source_system = 'C1X90ZB8N0';


I think now its ok ...
Re: Combining update statement. [message #301031 is a reply to message #301027] Tue, 19 February 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is worse. Your syntax is now not valid.
Did you try it?

Regards
Michel
Re: Combining update statement. [message #301036 is a reply to message #301031] Tue, 19 February 2008 00:47 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

No yaar... havent executed yet .. thought it might work.. so it won't work-a?

please help me to solve this problem ... Sad

Re: Combining update statement. [message #301045 is a reply to message #301036] Tue, 19 February 2008 01:07 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems we already answered what we can with what you posted.

Regards
Michel
Previous Topic: CHECK Constraint
Next Topic: Calling stored procedure from multiple clients?
Goto Forum:
  


Current Time: Sun Dec 11 00:47:28 CST 2016

Total time taken to generate the page: 0.08074 seconds