Please help with subqueries in update statement 1

[sql99]

Hello,

I have the following update statement:

UPDATE person per
SET per.start_date = (SELECT emp.start_date
FROM employee emp
WHERE per.ident = emp.ident);


Can you please tell me why this update statement isn't working? It's updating all the start_dates in the person table instead of the 20 that are in the employee table even though I specified the ident columns to match.

Thanks in advance,
sql99

 

[SantaMufasa]

SQL,

The problem is with SQL's "two-stage" treatment of your update. We'll call "Stage 1" the "update-value-gathering subquery" and "Stage 2" (which is missing from your Update) is the "range-limiting subquery". Since you are missing "Stage 2", Oracle's default result is to update every row in the table. Here, then, is how you can accomplish what you want:

 UPDATE person per
     SET per.start_date = (SELECT emp.start_date 
                              FROM employee emp
                             WHERE per.ident = emp.ident)
     WHERE exists = (SELECT 'x' FROM employee emp
                      WHERE per.ident = emp.ident);

Try that and let us know if it resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:44 (06Dec04) UTC (aka "GMT" and "Zulu"),
@ 15:44 (06Dec04) Mountain Time

 

[SantaMufasa]

Silly me...as soon as I hit the [Submit] key, I noticed a slight syntax error in my code suggestion: there should be no "=" following "exists". It should read:

UPDATE person per
     SET per.start_date = (SELECT emp.start_date 
                              FROM employee emp
                             WHERE per.ident = emp.ident)
     WHERE exists (SELECT 'x' FROM employee emp
                      WHERE per.ident = emp.ident);

Sorry.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:48 (06Dec04) UTC (aka "GMT" and "Zulu"),
@ 15:48 (06Dec04) Mountain Time

 

[sql99]

Thanks Dave. Of course it worked....I really appreciate your help...

sql99