Help on a UPDATE query

[shorti]

Hello again. I previously inquired about updating 1000 records at a
time in a script (not using cursors). Now I have to take it one step
further. I want to update records from two different records at once.
I have half of the step done but cant seem to fit the seconds part in
anywhere:

db2 "update (select * from EMPLOY where NAME IN (select NAME from
ACCOUNT where IDSTATUS <-1 fetch first 1000 rows only)) set
WORKIN='N', data_TITLED='N', COMMIS=100"

This works fine in the loop for the first 1000 records it finds in the
ACCOUNTs table. The update of the columns in the EMPLOY table work.
But...I want to set the IDSTATUS column in the ACCOUNT table to -1 at
the same time.

Any advise on an easy way to do this?

By the way, I tried two separate queries...this one and one to do the
update on the ACCOUNT table but it seems the second update does not
always give me the same results as the first update...so this gets me
in trouble.

Last, is there any good resources for complex db2 queries?

DB2 UDB 8.2 / AIX 5.4

[Serge Rielau]

shorti wrote:

Hello again. I previously inquired about updating 1000 records at a
time in a script (not using cursors). Now I have to take it one step
further. I want to update records from two different records at once.
I have half of the step done but cant seem to fit the seconds part in
anywhere:

db2 "update (select * from EMPLOY where NAME IN (select NAME from
ACCOUNT where IDSTATUS <-1 fetch first 1000 rows only)) set
WORKIN='N', data_TITLED='N', COMMIS=100"

This works fine in the loop for the first 1000 records it finds in the
ACCOUNTs table. The update of the columns in the EMPLOY table work.
But...I want to set the IDSTATUS column in the ACCOUNT table to -1 at
the same time.

Any advise on an easy way to do this?

By the way, I tried two separate queries...this one and one to do the
update on the ACCOUNT table but it seems the second update does not
always give me the same results as the first update...so this gets me
in trouble.

Last, is there any good resources for complex db2 queries?

DB2 UDB 8.2 / AIX 5.4

Something like this should do (not tested)
WITH updaccount(name)
AS (SELECT name FROM OLD TABLE(UPDATE (SELECT name, data_titled
FROM ACCOUNT
WHERE IDSTATUS <-1
FETCH FIRST 1000 ROWS ONLY)
AS U
SET IDSTATUS = -1))),
updemploy(dummy)
AS (SELECT 1 FROM OLD TABLE(UPDATE EMPLOY
SET WORKIN='N', data-titleid='N'
COMMIS=100
WHERE name IN (SELECT name
FROM updaccount)))
SELECT dummy FROM updemploy

I can teach you in person in Anaheim. Simply attend "SQL on Fire!"

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[ibadba]

Serge Rielau schrieb:

shorti wrote:

Hello again. I previously inquired about updating 1000 records at a
time in a script (not using cursors). Now I have to take it one step
further. I want to update records from two different records at once.
I have half of the step done but cant seem to fit the seconds part in
anywhere:

db2 "update (select * from EMPLOY where NAME IN (select NAME from
ACCOUNT where IDSTATUS <-1 fetch first 1000 rows only)) set
WORKIN='N', data_TITLED='N', COMMIS=100"

This works fine in the loop for the first 1000 records it finds in the
ACCOUNTs table. The update of the columns in the EMPLOY table work.
But...I want to set the IDSTATUS column in the ACCOUNT table to -1 at
the same time.

Any advise on an easy way to do this?

By the way, I tried two separate queries...this one and one to do the
update on the ACCOUNT table but it seems the second update does not
always give me the same results as the first update...so this gets me
in trouble.

Last, is there any good resources for complex db2 queries?

DB2 UDB 8.2 / AIX 5.4

Something like this should do (not tested)
WITH updaccount(name)
AS (SELECT name FROM OLD TABLE(UPDATE (SELECT name, data_titled
FROM ACCOUNT
WHERE IDSTATUS <-1
FETCH FIRST 1000 ROWS ONLY)
AS U
SET IDSTATUS = -1))),
updemploy(dummy)
AS (SELECT 1 FROM OLD TABLE(UPDATE EMPLOY
SET WORKIN='N', data-titleid='N'
COMMIS=100
WHERE name IN (SELECT name
FROM updaccount)))
SELECT dummy FROM updemploy

I can teach you in person in Anaheim. Simply attend "SQL on Fire!"

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

For the last part, the best resource outside this newsgroup and
Developerworks articles is the DB2 Cookbook
http://mysite.verizon.net/Graeme_Birchall/id1.html