SQL Challenge: Searched UPDATE

Jim Drewe

SQL Challenge: Searched UPDATE
Okay, you SQL gurus, here is a challenge for you.

I know this problem can be solved in a program, but I am wondering if it can be
solved using ad hoc SQL.

What I am hoping to do is issue a searched UPDATE to update a table based on
criteria from three other tables. The problem as I see it seems to be that
UPDATE SET does not support more than one table in its WHERE search clause. If
it does, I am not sure how to construct the correlation identifiers.

Here is the basic logic for the update (with the join columns immediately
following).

IF TABLE1.NONKEY_COL1 = TABLE2.NONKEY_COL1
THEN
SET TABLE3.NONKEY_COL1 = 'L'
ELSE
IF TABLE1.NONKEY_COL1 = TABLE4.NONKEY_COL1
THEN
SET TABLE3.NONKEY_COL1 = 'R'
ELSE
SET TABLE3.NONKEY_COL1 = 'F'
ENDIF
ENDIF

The join columns (with complete transitive closure) will be:

WHERE
TABLE3.TABLE1_FK_COL = TABLE1.PK_COL
AND TABLE3.TABLE2_FK_COL = TABLE2.PK_COL
AND TABLE2.PK_COL = TABLE4.PK_COL

My thought is that it cannot be done. But I will give three cheers to the one
who can provide a solution.

Thanks!

Jim Drewe
DBA



Suresh Sane

Re: SQL Challenge: Searched UPDATE
(in response to Jim Drewe)
OK James, I'll bite.

How aboiut setiing the value to F first and then updating it to L or R as
the case may be. Example:

Step1: update tab3 set nokeycol = 'f';

Step2: update tab3 set nonkeycol = 'r'
where exists
(select 1 from tab1, tab4
where
tab1.nonkeycol1 = tab4.nokeycol
and tab3.keycol = tab1.keycol
and tab4.keycol = tab1.keycol)

Step 3: similar sql for setting to 'l'.

I assume steps 2 & 3 can be run in any order but to translate your
requirements exactly, need to execute the code for 'r' before the 'l' in
case BOTH conditions are true.

It may be possible to these in 1 step (update with a subselect to sysdummy1)
if you need it but you stated only "ad hoc". Perhaps someone else will come
up with such a solution.

HTH.

Suresh


>From: James Drewe <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: SQL Challenge: Searched UPDATE
>Date: Wed, 19 Dec 2001 10:26:38 -0700
>
>Okay, you SQL gurus, here is a challenge for you.
>
>I know this problem can be solved in a program, but I am wondering if it
>can be
>solved using ad hoc SQL.
>
>What I am hoping to do is issue a searched UPDATE to update a table based
>on
>criteria from three other tables. The problem as I see it seems to be that
>UPDATE SET does not support more than one table in its WHERE search clause.
> If
>it does, I am not sure how to construct the correlation identifiers.
>
>Here is the basic logic for the update (with the join columns immediately
>following).
>
>IF TABLE1.NONKEY_COL1 = TABLE2.NONKEY_COL1
>THEN
> SET TABLE3.NONKEY_COL1 = 'L'
>ELSE
> IF TABLE1.NONKEY_COL1 = TABLE4.NONKEY_COL1
> THEN
> SET TABLE3.NONKEY_COL1 = 'R'
> ELSE
> SET TABLE3.NONKEY_COL1 = 'F'
> ENDIF
>ENDIF
>
>The join columns (with complete transitive closure) will be:
>
>WHERE
> TABLE3.TABLE1_FK_COL = TABLE1.PK_COL
> AND TABLE3.TABLE2_FK_COL = TABLE2.PK_COL
> AND TABLE2.PK_COL = TABLE4.PK_COL
>
>My thought is that it cannot be done. But I will give three cheers to the
>one
>who can provide a solution.
>
>Thanks!
>
>Jim Drewe
>DBA
>
>
>
>http://www.ryci.com/db2-l. The owners of the list can be reached at
>[login to unmask email]


_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com



Terry Purcell

Re: SQL Challenge: Searched UPDATE
(in response to Suresh Sane)
Jim,

If I read this correctly then you want to update TABLE3. Then simply:

UPDATE TABLE3 T3
SET LOCATION =
(SELECT CASE WHEN T1.NONKEY_COL = T2.NONKEY_COL THEN 'L'
WHEN T1.NONKEY_COL = T4.NONKEY_COL THEN 'R'
ELSE 'F' END
FROM (SELECT DISTINCT NONKEY_COL
FROM TABLE1
WHERE NONKEY_COL = T3.NONKEY_COL) AS T1
FULL JOIN
(SELECT DISTINCT NONKEY_COL
FROM TABLE2
WHERE NONKEY_COL = T3.NONKEY_COL) AS T2
ON T1.NONKEY_COL = T2.NONKEY_COL
FULL JOIN
(SELECT DISTINCT NONKEY_COL
FROM TABLE4
WHERE NONKEY_COL = T3.NONKEY_COL) AS T4
ON T4.NONKEY_COL = COALESCE(T1.NONKEY_COL, T2.NONKEY_COL));

The DISTINCTs may not be needed if the column is unique in each table, or
you may want to just code one on the CASE, or even GROUP BYs which have
better capability to avoid a sort with available indexes.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
James Drewe
Sent: Wednesday, December 19, 2001 11:27 AM
To: [login to unmask email]
Subject: SQL Challenge: Searched UPDATE


Okay, you SQL gurus, here is a challenge for you.

I know this problem can be solved in a program, but I am wondering if it can
be
solved using ad hoc SQL.

What I am hoping to do is issue a searched UPDATE to update a table based
on
criteria from three other tables. The problem as I see it seems to be that
UPDATE SET does not support more than one table in its WHERE search clause.
If
it does, I am not sure how to construct the correlation identifiers.

Here is the basic logic for the update (with the join columns immediately
following).

IF TABLE1.NONKEY_COL1 = TABLE2.NONKEY_COL1
THEN
SET TABLE3.NONKEY_COL1 = 'L'
ELSE
IF TABLE1.NONKEY_COL1 = TABLE4.NONKEY_COL1
THEN
SET TABLE3.NONKEY_COL1 = 'R'
ELSE
SET TABLE3.NONKEY_COL1 = 'F'
ENDIF
ENDIF

The join columns (with complete transitive closure) will be:

WHERE
TABLE3.TABLE1_FK_COL = TABLE1.PK_COL
AND TABLE3.TABLE2_FK_COL = TABLE2.PK_COL
AND TABLE2.PK_COL = TABLE4.PK_COL

My thought is that it cannot be done. But I will give three cheers to the
one
who can provide a solution.

Thanks!

Jim Drewe
DBA

================






teldb2kals

Re: SQL Challenge: Searched UPDATE
(in response to Terry Purcell)
Jim,

Can u try this ?

update table3 set nonkey_col1 =
(select case when TABLE1.NONKEY_COL1 = TABLE2.NONKEY_COL1 then 'L'
when TABLE1.NONKEY_COL1 = TABLE4.NONKEY_COL1 then 'R'
else 'F'
end
from table1, table2, table4
where
TABLE3.TABLE1_FK_COL = TABLE1.PK_COL
AND TABLE3.TABLE2_FK_COL = TABLE2.PK_COL
AND TABLE2.PK_COL = TABLE4.PK_COL
)

Cheers,
Kals

-----Original Message-----
From: James Drewe [SMTP:[login to unmask email]
Sent: Thursday, December 20, 2001 4:27 AM
To: [login to unmask email]
Subject: SQL Challenge: Searched UPDATE

Okay, you SQL gurus, here is a challenge for you.

I know this problem can be solved in a program, but I am wondering if
it can be
solved using ad hoc SQL.

What I am hoping to do is issue a searched UPDATE to update a table
based on
criteria from three other tables. The problem as I see it seems to be
that
UPDATE SET does not support more than one table in its WHERE search
clause. If
it does, I am not sure how to construct the correlation identifiers.

Here is the basic logic for the update (with the join columns
immediately
following).

IF TABLE1.NONKEY_COL1 = TABLE2.NONKEY_COL1
THEN
SET TABLE3.NONKEY_COL1 = 'L'
ELSE
IF TABLE1.NONKEY_COL1 = TABLE4.NONKEY_COL1
THEN
SET TABLE3.NONKEY_COL1 = 'R'
ELSE
SET TABLE3.NONKEY_COL1 = 'F'
ENDIF
ENDIF

The join columns (with complete transitive closure) will be:

WHERE
TABLE3.TABLE1_FK_COL = TABLE1.PK_COL
AND TABLE3.TABLE2_FK_COL = TABLE2.PK_COL
AND TABLE2.PK_COL = TABLE4.PK_COL

My thought is that it cannot be done. But I will give three cheers to
the one
who can provide a solution.

Thanks!

Jim Drewe
DBA



----------------
Powered by telstra.com



Terry Purcell

Re: SQL Challenge: Searched UPDATE
(in response to teldb2kals)
Oops...I messed up the join predicates and therefore assumed this was more
complex than it actually is. If these are really foreign key to primary key
comparisons, then the FULL JOINs are not required, and can simply be inner
joins. Also, if this is truly primary keys, then the DISTINCTs aren't
required either.

Kals has sent in a much simpler version.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: Terry Purcell [mailto:[login to unmask email]
Sent: Wednesday, December 19, 2001 4:50 PM
To: DB2 Data Base Discussion List
Subject: RE: SQL Challenge: Searched UPDATE


Jim,

If I read this correctly then you want to update TABLE3. Then simply:

UPDATE TABLE3 T3
SET LOCATION =
(SELECT CASE WHEN T1.NONKEY_COL = T2.NONKEY_COL THEN 'L'
WHEN T1.NONKEY_COL = T4.NONKEY_COL THEN 'R'
ELSE 'F' END
FROM (SELECT DISTINCT NONKEY_COL
FROM TABLE1
WHERE NONKEY_COL = T3.NONKEY_COL) AS T1
FULL JOIN
(SELECT DISTINCT NONKEY_COL
FROM TABLE2
WHERE NONKEY_COL = T3.NONKEY_COL) AS T2
ON T1.NONKEY_COL = T2.NONKEY_COL
FULL JOIN
(SELECT DISTINCT NONKEY_COL
FROM TABLE4
WHERE NONKEY_COL = T3.NONKEY_COL) AS T4
ON T4.NONKEY_COL = COALESCE(T1.NONKEY_COL, T2.NONKEY_COL));

The DISTINCTs may not be needed if the column is unique in each table, or
you may want to just code one on the CASE, or even GROUP BYs which have
better capability to avoid a sort with available indexes.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
James Drewe
Sent: Wednesday, December 19, 2001 11:27 AM
To: [login to unmask email]
Subject: SQL Challenge: Searched UPDATE


Okay, you SQL gurus, here is a challenge for you.

I know this problem can be solved in a program, but I am wondering if it can
be
solved using ad hoc SQL.

What I am hoping to do is issue a searched UPDATE to update a table based
on
criteria from three other tables. The problem as I see it seems to be that
UPDATE SET does not support more than one table in its WHERE search clause.
If
it does, I am not sure how to construct the correlation identifiers.

Here is the basic logic for the update (with the join columns immediately
following).

IF TABLE1.NONKEY_COL1 = TABLE2.NONKEY_COL1
THEN
SET TABLE3.NONKEY_COL1 = 'L'
ELSE
IF TABLE1.NONKEY_COL1 = TABLE4.NONKEY_COL1
THEN
SET TABLE3.NONKEY_COL1 = 'R'
ELSE
SET TABLE3.NONKEY_COL1 = 'F'
ENDIF
ENDIF

The join columns (with complete transitive closure) will be:

WHERE
TABLE3.TABLE1_FK_COL = TABLE1.PK_COL
AND TABLE3.TABLE2_FK_COL = TABLE2.PK_COL
AND TABLE2.PK_COL = TABLE4.PK_COL

My thought is that it cannot be done. But I will give three cheers to the
one
who can provide a solution.

Thanks!

Jim Drewe
DBA

================