From: shorti on
I am on DB2 V9.5 on AIX. The following is a query I want to use for
an update. It works fine except when I add the "Fetch first 1000
rows"

db2 "UPDATE tab1 SET (col1, col2, col3) = (1, 'Y', 'Y')
WHERE col1 <> 1 AND
name IN ( SELECT name FROM tab2 WHERE colA IN (SELECT name
FROM tab3 WHERE colB IN
('FIRST', 'SECOND'))) AND
name NOT IN (SELECT name FROM tab2 WHERE colA IN (SELECT name
FROM tab3 WHERE colB IN
('NINE', 'TEN', 'FIVE'))) FETCH FIRST 1000 ROWS
ONLY"

I received the following errors :

DB20104N An unexpected Token "update tab1 set (" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include:
"<query_expr_body>". SQLSTATE=42601

I can see how it would be confusing where the 1000 rows should be
fetched due to the "name IN and name NOT IN" So I ttried to dump the
outcome of these two conditions in a results table:

WITH temp AS
(SELECT name FROM tab1 WHERE
name IN ( SELECT name FROM tab2 WHERE colA IN (SELECT name FROM
tab3 WHERE colB IN
('FIRST', 'SECOND'))) AND
name NOT IN (SELECT name FROM tab2 WHERE colA IN (SELECT name
FROM tab3 WHERE colB IN
('NINE', 'TEN', 'FIVE')))
)
SELECT name from TEMP WHERE name IN (SELECT name FROM tab1 WHERE col1
<> 1)
FETCH FIRST 1000 ROWS ONLY

Again, this works. However, I need to make this an update but I
cannot find hardly any examples of how to do this using the common
table expression. I see a ton of SELECT examples. I tried adding the
UPDATE instead of the SELECT at the end :

UPDATE tab1 set col1=1 WHERE col1 <>1 AND name IN (SELECT name FROM
temp)

Anyone have a suggestion?

From: danfan46 on
shorti wrote:
> I am on DB2 V9.5 on AIX. The following is a query I want to use for
> an update. It works fine except when I add the "Fetch first 1000
> rows"
>
> db2 "UPDATE tab1 SET (col1, col2, col3) = (1, 'Y', 'Y')
> WHERE col1 <> 1 AND
> name IN ( SELECT name FROM tab2 WHERE colA IN (SELECT name
> FROM tab3 WHERE colB IN
> ('FIRST', 'SECOND'))) AND
> name NOT IN (SELECT name FROM tab2 WHERE colA IN (SELECT name
> FROM tab3 WHERE colB IN
> ('NINE', 'TEN', 'FIVE'))) FETCH FIRST 1000 ROWS
> ONLY"
>
> I received the following errors :
>
> DB20104N An unexpected Token "update tab1 set (" was found following
> "BEGIN-OF-STATEMENT". Expected tokens may include:
> "<query_expr_body>". SQLSTATE=42601
>
> I can see how it would be confusing where the 1000 rows should be
> fetched due to the "name IN and name NOT IN" So I ttried to dump the
> outcome of these two conditions in a results table:
>
> WITH temp AS
> (SELECT name FROM tab1 WHERE
> name IN ( SELECT name FROM tab2 WHERE colA IN (SELECT name FROM
> tab3 WHERE colB IN
> ('FIRST', 'SECOND'))) AND
> name NOT IN (SELECT name FROM tab2 WHERE colA IN (SELECT name
> FROM tab3 WHERE colB IN
> ('NINE', 'TEN', 'FIVE')))
> )
> SELECT name from TEMP WHERE name IN (SELECT name FROM tab1 WHERE col1
> <> 1)
> FETCH FIRST 1000 ROWS ONLY
>
> Again, this works. However, I need to make this an update but I
> cannot find hardly any examples of how to do this using the common
> table expression. I see a ton of SELECT examples. I tried adding the
> UPDATE instead of the SELECT at the end :
>
> UPDATE tab1 set col1=1 WHERE col1 <>1 AND name IN (SELECT name FROM
> temp)
>
> Anyone have a suggestion?
>
Try this:
Merge into tab1
using
( SELECT name FROM tab1
WHERE name IN
( SELECT name
FROM tab2
WHERE colA IN
( SELECT name
FROM tab3
WHERE colB IN ('FIRST', 'SECOND')
)
)
AND name NOT IN
( SELECT name
FROM tab2
WHERE colA IN
( SELECT name
FROM tab3
WHERE colB IN ('NINE', 'TEN', 'FIVE')
)
)
FETCH FIRST 1000 ROWS ONLY
) T1
on tab1.name = T1.name
and tab1.col1 <> 1
when matched then
update
set col1 = 1
,col2 = 'Y'
,col3 = 'Y'
;
/dg
From: Will Honea on
danfan46 wrote:

> shorti wrote:
>> I am on DB2 V9.5 on AIX. The following is a query I want to use for
>> an update. It works fine except when I add the "Fetch first 1000
>> rows"
>>
>> db2 "UPDATE tab1 SET (col1, col2, col3) = (1, 'Y', 'Y')
>> WHERE col1 <> 1 AND
>> name IN ( SELECT name FROM tab2 WHERE colA IN (SELECT name
>> FROM tab3 WHERE colB IN
>> ('FIRST', 'SECOND'))) AND
>> name NOT IN (SELECT name FROM tab2 WHERE colA IN (SELECT name
>> FROM tab3 WHERE colB IN
>> ('NINE', 'TEN', 'FIVE'))) FETCH FIRST 1000 ROWS
>> ONLY"
>>
>> I received the following errors :
>>
>> DB20104N An unexpected Token "update tab1 set (" was found following
>> "BEGIN-OF-STATEMENT". Expected tokens may include:
>> "<query_expr_body>". SQLSTATE=42601
>>
>> I can see how it would be confusing where the 1000 rows should be
>> fetched due to the "name IN and name NOT IN" So I ttried to dump the
>> outcome of these two conditions in a results table:
>>
>> WITH temp AS
>> (SELECT name FROM tab1 WHERE
>> name IN ( SELECT name FROM tab2 WHERE colA IN (SELECT name FROM
>> tab3 WHERE colB IN
>> ('FIRST', 'SECOND'))) AND
>> name NOT IN (SELECT name FROM tab2 WHERE colA IN (SELECT name
>> FROM tab3 WHERE colB IN
>> ('NINE', 'TEN', 'FIVE')))
>> )
>> SELECT name from TEMP WHERE name IN (SELECT name FROM tab1 WHERE col1
>> <> 1)
>> FETCH FIRST 1000 ROWS ONLY
>>
>> Again, this works. However, I need to make this an update but I
>> cannot find hardly any examples of how to do this using the common
>> table expression. I see a ton of SELECT examples. I tried adding the
>> UPDATE instead of the SELECT at the end :
>>
>> UPDATE tab1 set col1=1 WHERE col1 <>1 AND name IN (SELECT name FROM
>> temp)
>>
>> Anyone have a suggestion?
>>
> Try this:
> Merge into tab1
> using
> ( SELECT name FROM tab1
> WHERE name IN
> ( SELECT name
> FROM tab2
> WHERE colA IN
> ( SELECT name
> FROM tab3
> WHERE colB IN ('FIRST', 'SECOND')
> )
> )
> AND name NOT IN
> ( SELECT name
> FROM tab2
> WHERE colA IN
> ( SELECT name
> FROM tab3
> WHERE colB IN ('NINE', 'TEN', 'FIVE')
> )
> )
> FETCH FIRST 1000 ROWS ONLY
> ) T1
> on tab1.name = T1.name
> and tab1.col1 <> 1
> when matched then
> update
> set col1 = 1
> ,col2 = 'Y'
> ,col3 = 'Y'
> ;
> /dg

For generality, I would suggest that you add
GROUP BY name
before the FETCH FIRST 1000 ROWS ONLY line in the using clause. Merge is
enormously useful but the implicit join in the "on" clause will cause
problems with duplicated names in the select list.

--
Will Honea

From: ChrisC on
Another option:

UPDATE
(select * from tab1 WHERE col1 <> 1 AND
name IN ( SELECT name FROM tab2 WHERE colA IN (SELECT name
FROM tab3 WHERE colB IN
('FIRST', 'SECOND'))) AND
name NOT IN (SELECT name FROM tab2 WHERE colA IN (SELECT name
FROM tab3 WHERE colB IN
('NINE', 'TEN', 'FIVE'))) FETCH FIRST 1000 ROWS
ONLY) table_to_update
SET (col1, col2, col3) = (1, 'Y', 'Y')
 | 
Pages: 1
Prev: very dynamic cursor
Next: DB2 join operator