473,146 Members | 2,741 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,146 software developers and data experts.

Help on a UPDATE query

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

Aug 12 '06 #1
2 12511
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/
Aug 12 '06 #2
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

Aug 12 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Somerset Bob | last post by:
I've posted my query at the phpbb forum, where I got half an answer and then no more. I posted it again at another phpbb forum, where I got half an answer and no more. I posted it in alt.php, where...
0
by: Somerset Bob | last post by:
I've posted my query at the phpbb forum, where I got half an answer and then no more. I posted it again at another phpbb forum, where I got half an answer and no more. I posted it in alt.php, where...
4
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline...
6
by: John Baker | last post by:
Hi: As those who have looked at this newsgroup recently will realize, I am a neophyte with Access, although I have experienced with Approach (the Lotus product). There are things I could easily...
9
by: hope | last post by:
Hi Access 97 I'm lost on this code please can you help ================================= Below is some simple code that will concatenate a single field's value from multiple records into a...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
4
by: Laura | last post by:
Here's the situation: I'm trying to use an update query to copy data from one row to another. Here is the situation: I have 5 companies that are linked to each other. I need to show all 5...
3
by: Shapper | last post by:
Hello, I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine. The update is not. I checked and my database has all...
6
by: Takeadoe | last post by:
Dear NG, Can someone assist me with writing the little code that is needed to run an update table query each time the database is opened? From what I've been able to glean from this group, the...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
2
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
3
by: efe2023 | last post by:
Hello Everybody, There is SQL Server 2008 R2 database with MS Access front-end (linked ODBC tables). I can see all linked tables in MS Access and can add new rows. However, when trying to update...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
3
by: jimatqsi | last post by:
So, I had this bright idea today that didn't quite work out. Or maybe it is working out but requires me to change a habit. I'm working on a form that will be a sub-form of another form. During...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.