UPDATE with correlated subquery

[Massimo Pettenò]

Is there a possibility to UPDATE a column (C1) of a table (T1)
using values from a column (C2) of a table (T2)
joining the tables T1 and T2 based on a join key
(WHERE T1.CK = T2.CK)?

Thanx.
Max

[Rolf Mittag]

This is valid SQL on DB2 for OS/400 (from the IBM samples as far as i
remember)

UPDATE PROJECT SET DEPTNO = (SELECT WORKDEPT FROM EMPLOYEE WHERE
PROJECT.RESPEMP = EMPLOYEE.EMPNO) WHERE RESPEMP='000030'
UPDATE PROJECT SET (WORKDEPT, PHONENO, JOB) = (SELECT WORKDEPT, PHONENO, JOB
FROM EMPLOYEE WHERE PROJECT.RESPEMP = EMPLOYEE.EMPNO) WHERE RESPEMP='000030'

UPDATE CL_SCHED SET ROW = (SELECT * FROM MYCOPY WHERE CL_SCHED.CLASS_CODE =
MYCOPY.CLASS_CODE)

hth

R.

--

Dipl.Inf.(FH) Rolf P Mittag
IBM Partner In Development
Leipziger Str. 50
D-69214 Eppelheim
eMl: r...@r-m-e-d-v.de
Fon: +49 (6221) 76 78 60
Fax: +49 (6221) 76 80 26

"Massimo Pettenņ" <Petteno...@generali.it> schrieb im Newsbeitrag
news:39E48D44...@generali.it...

[Serge Rielau]

Hi,

You have to repeat the correlated subquery in the assignemnt and teh
wheer clause.

UPDATE T1 SET c1 = (select c2 from t2 where t1.pk = t2.pk)
WHERE EXISTS (select * from t2 where t1.pk = t2.pk);

In DB2 for workstation this query will be rewritten internally to
UPDATE T1 SET c1 = c2 FROM T1, T2 WHERE t1.pk = t2.pk;
(Which is unfortunately not in the SQL standard, thus not exposed by
DB2).
So teh query will perform faster than it looks like ;-)

Cheers
Serge
--
Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support

[Blair Kenneth Adamache]

This syntax is valid for DB2 on Unix, Windows and OS/2:

update adress2 set (HOUSENUMBER_BUILDINGNAME)=
(select housenumber from adress1
where adress2.id=adress1.id and housenumber)

[Joe Celko]

>> Is there a possibility to UPDATE a column (C1) of a table (T1)
using values from a column (C2) of a table (T2) joining the tables T1
and T2 based on a join key (WHERE T1.CK = T2.CK)? <<

Yes, but you have to careful.

1) UPDATE T1
SET c1 = (SELECT c2
FROM T2
WHERE T1.ck = T2.ck);

This will set T1.c1 to NULL whenever there is no match. The scalar
subquery is empty and it becomes a NULL.

2) UPDATE T1
SET c1 = (SELECT c2
FROM T2
WHERE T1.ck = T2.ck)
WHERE EXISTS (SELECT *
FROM T2
WHERE T1.ck = T2.ck);

This will leave the unmatched rows alone, but it picks out a subset of
the table T1 first.

3) UPDATE T1
SET c1 = COALESCE ((SELECT c2
FROM T2
WHERE T1.ck = T2.ck), T1.c1);

This will also leave the unmatched rows alone, but it will do a table
scan on T1.

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.

[Jeremy...@unisystems.co.uk]

Joe Celko wrote.....

<<
3) UPDATE T1
SET c1 = COALESCE ((SELECT c2
FROM T2
WHERE T1.ck = T2.ck), T1.c1);

This will also leave the unmatched rows alone, but it will do a table
scan on T1.
>>

Even better, put the COALESCE inside the subquery select list - that way
the solution "scales" to multi-column updates. For example:

update t2
set
(
c2,
c3
) =
(
select
coalesce(t1.c3, t2.c2),
coalesce(t1.c2, t2.c3)
from
t1
where
t1.c1 = t2.c1
)
where
c1 <> 1;

Jeremy Rickard

[Massimo Pettenò]

I understand. It doesn't work with DB2 for OS/390 V.5.
I try it with DB2 for OS/390 V.6 and
IT WORKS!!!

Thanx.
Max

[Joe Celko]

>> Even better, put the COALESCE inside the subquery select list - that
way the solution "scales" to multi-column updates. For example:

UPDATE T2
SET (c2, c3)
= (SELECT COALESCE(T1.c2, T2.c2),COALESCE(T1.c3, T2.c3)
FROM T1
WHERE T1.c1 = T2.c1)
WHERE c1 <> 1;
<<

I like that one! I have to put it in the next edition of my book.