By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,745 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

update from a select

P: n/a
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.

Jul 12 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jul 12 '07 #2

P: n/a
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.
Jul 13 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.