update from a select

[gimme_this_gimme_that]

I use the following SQL statment to bring z_emp_id values to a
employee table:

update employee set z_emp_id =
(select z.emp_id from z.employee z where z.login=employee.login)

Upon executing this statement a warning appears (in AQT) saying all
the rows in the table will be modified.

Is this a benign message?

I would expect a warning, if any, to say how many rows would change.

The results (100 or so modified rows) appears to be correct.

I'd like to create an alternative SQL statment, something like:

update employee
set z_emp_id = z.emp_id
from (select emp_id from z.employee z where z.login in (select login
from z.employee)) z
where login = z.login

where z_emp_id is assigned from the z select.

This statement fails because DB2 doesn't understand FROM in this
context.

Might someone be so kind as to suggest a SQL statement that doesn't
impact all rows and has cleaner syntax?

Thanks.

[Serge Rielau]

gi*******************@yahoo.com wrote:

I use the following SQL statment to bring z_emp_id values to a
employee table:

update employee set z_emp_id =
(select z.emp_id from z.employee z where z.login=employee.login)

MERGE INTO employee
USING z.employee as z
ON z.login=employee.login
WHEN MATCHED THEN UPDATE SET z_emp_id = z.emp_id

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Brian Tkatch]

On Thu, 12 Jul 2007 12:31:30 -0700, "gi*******************@yahoo.com"
<gi*******************@yahoo.comwrote:

>update employee set z_emp_id =
(select z.emp_id from z.employee z where z.login=employee.login)

Upon executing this statement a warning appears (in AQT) saying all
the rows in the table will be modified.

Of course. The UPDATE has no WHERE clause, so there is nothing
excluding rows. Ergo, all rows will be hit. If it doesn't find a
match, it will SET it to NULL. Which, depending on the strategy, might
be a "Good Thing"(tm). Especially if it had a value that shouldn't be
there.

If you only want to hit the rows where there is a value found, use
WHERE EXISTS and repeat the subquery.

>update employee
set z_emp_id = z.emp_id
from (select emp_id from z.employee z where z.login in (select login
from z.employee)) z
where login = z.login

The FROM's sub-select is redundant. And, the FROM itself is not part
of an UPDATE statement. This is a database, not SQL Server.

B.