Home » SQL & PL/SQL » SQL & PL/SQL » Difficult update from sub query SQL statement
Difficult update from sub query SQL statement [message #284670] Fri, 30 November 2007 08:26 Go to next message
pstein
Messages: 7
Registered: February 2007
Junior Member
Hello,

I have problems to setup a SQL statement with the following
requirements:

I have two tables A and B which I can join and select some of the rows according to some requirements

SELECT * FROM A, B WHERE where A.ID = B.ID AND ....

Ok, fine at first.

Now I want to update column XX in table A for all those rows
which are found in the first SELECT statement. The new value should be taken from a column YY in table B.

The following comes close but does not work:

update A set A.XX = (SELECT B.YY FROM A, B WHERE where A.ID = B.ID AND .....)

The problem here is that the subquery returns multiple rows. But I want to assign only 1 value.

How can I select only the relevant value for the update assignment? BTW: ID is unique.

[Updated on: Fri, 30 November 2007 08:28]

Report message to a moderator

Re: Difficult update from sub query SQL statement [message #284672 is a reply to message #284670] Fri, 30 November 2007 08:34 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
The way you wrote your update: UPDATE each record of table A

I think you're trying to achieve this:

UPDATE A
SET A.XX = 
 (
 SELECT B.YY
 FROM B
 WHERE B.ID = A.ID
 )
Re: Difficult update from sub query SQL statement [message #284701 is a reply to message #284672] Fri, 30 November 2007 11:07 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This also update each record in A even those with no associated row in B (in this XX is set to null).

update (select a.id aid, b.id bid, a.xx, b.yy
        from a, b
        where a.id = b.id)
set xx = yy
/

This is one way to set a.xx from b.yy where both id exist.

Regards
Michel
Previous Topic: Find the date between (merged)
Next Topic: Modify query based on variable
Goto Forum:
  


Current Time: Sun Dec 04 22:40:38 CST 2016

Total time taken to generate the page: 0.09248 seconds