473,322 Members | 1,911 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,322 software developers and data experts.

Update Multiple Rows

I'm trying to UPDATE multiple rows in a single statement. I've seen some examples but to me they don't seem to apply to my scenario. Below is my original SQL:

UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP))

When I run this query, I get the multiple recordset error.

I would welcome any suggestions or ideas on how to rectify the problem.

Thanks in advance...
Nov 12 '05 #1
10 12963
"Anthony Robinison" <an*****@yahoo.com> wrote in message
news:e9*****************@twister.rdc-kc.rr.com...
I'm trying to UPDATE multiple rows in a single statement. I've seen some
examples but to me they don't seem to apply to my scenario. Below is my
original SQL:

UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP))

When I run this query, I get the multiple recordset error.

I would welcome any suggestions or ideas on how to rectify the problem.

Thanks in advance...
-------------------------------------------------
I suspect that the problem is in the subselect, which is returning more than
one value.
Nov 12 '05 #2
"Anthony Robinison" <an*****@yahoo.com> wrote in message
news:e9*****************@twister.rdc-kc.rr.com...
I'm trying to UPDATE multiple rows in a single statement. I've seen some
examples but to me they don't seem to apply to my scenario. Below is my
original SQL:

UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP))

When I run this query, I get the multiple recordset error.

I would welcome any suggestions or ideas on how to rectify the problem.

Thanks in advance...
-------------------------------------------------
I suspect that the problem is in the subselect, which is returning more than
one value.
Nov 12 '05 #3
I've seen examples where they were able to get around the more than one
record issue...

Anyone seen this or have any ideas?

"Mark A" <ma@switchboard.net> wrote in message
news:gf*****************@news.uswest.net...
"Anthony Robinison" <an*****@yahoo.com> wrote in message
news:e9*****************@twister.rdc-kc.rr.com...
I'm trying to UPDATE multiple rows in a single statement. I've seen some
examples but to me they don't seem to apply to my scenario. Below is my
original SQL:

UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP))

When I run this query, I get the multiple recordset error.

I would welcome any suggestions or ideas on how to rectify the problem.

Thanks in advance...
-------------------------------------------------
I suspect that the problem is in the subselect, which is returning more than one value.

Nov 12 '05 #4
I've seen examples where they were able to get around the more than one
record issue...

Anyone seen this or have any ideas?

"Mark A" <ma@switchboard.net> wrote in message
news:gf*****************@news.uswest.net...
"Anthony Robinison" <an*****@yahoo.com> wrote in message
news:e9*****************@twister.rdc-kc.rr.com...
I'm trying to UPDATE multiple rows in a single statement. I've seen some
examples but to me they don't seem to apply to my scenario. Below is my
original SQL:

UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP))

When I run this query, I get the multiple recordset error.

I would welcome any suggestions or ideas on how to rectify the problem.

Thanks in advance...
-------------------------------------------------
I suspect that the problem is in the subselect, which is returning more than one value.

Nov 12 '05 #5
Ian
Anthony Robinison wrote:
I'm trying to UPDATE multiple rows in a single statement. I've seen some
examples but to me they don't seem to apply to my scenario. Below is my
original SQL:

*/UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP))/*
*//*
When I run this query, I get the multiple recordset error.

I would welcome any suggestions or ideas on how to rectify the problem.

Thanks in advance...


....
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID in (select ...)
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #6
Ian
Anthony Robinison wrote:
I'm trying to UPDATE multiple rows in a single statement. I've seen some
examples but to me they don't seem to apply to my scenario. Below is my
original SQL:

*/UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP))/*
*//*
When I run this query, I get the multiple recordset error.

I would welcome any suggestions or ideas on how to rectify the problem.

Thanks in advance...


....
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID in (select ...)
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #7
I can't believe it was that easy!!! Why didn't I think of that - I was
making it way too complicated...

Thanks Ian!

"Ian" <ia*****@mobileaudio.com> wrote in message
news:40**********@corp.newsgroups.com...
Anthony Robinison wrote:
I'm trying to UPDATE multiple rows in a single statement. I've seen some
examples but to me they don't seem to apply to my scenario. Below is my
original SQL:

*/UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP))/*
*//*
When I run this query, I get the multiple recordset error.

I would welcome any suggestions or ideas on how to rectify the problem.

Thanks in advance...


...
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID in (select ...)
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Nov 12 '05 #8
I can't believe it was that easy!!! Why didn't I think of that - I was
making it way too complicated...

Thanks Ian!

"Ian" <ia*****@mobileaudio.com> wrote in message
news:40**********@corp.newsgroups.com...
Anthony Robinison wrote:
I'm trying to UPDATE multiple rows in a single statement. I've seen some
examples but to me they don't seem to apply to my scenario. Below is my
original SQL:

*/UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID =
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP))/*
*//*
When I run this query, I get the multiple recordset error.

I would welcome any suggestions or ideas on how to rectify the problem.

Thanks in advance...


...
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID in (select ...)
Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Nov 12 '05 #9
Also take a look at the MERGE statement. It doesn't require the double
select

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #10
Also take a look at the MERGE statement. It doesn't require the double
select

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #11

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

Similar topics

2
by: Rima | last post by:
I have the following two tables : table a (commit_id, capital_market_id, chg_lst_date ) table b (b_seq_id,
7
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a...
8
by: Lauren Quantrell | last post by:
In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID: UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON...
8
by: Jason | last post by:
I have a table that matches up Securities and Exchanges. Individual securities can belong on multiple exchanges. One of the columns, named PrimaryExchangeFlag, indicates if a particular exchange is...
18
by: Bill Smith | last post by:
The initial row is inserted with the colPartNum column containing a valid LIKE pattern, such as (without the single quotes) 'AB%DE'. I want to update the column value with the results of a query...
2
by: Christopher | last post by:
Greetings, Fairly new to c# and Im looking for pointers on a few topics: Im curious as to general methods to update multiple rows in a database. Currently Im thinking of just allowing a user...
4
by: Ed L. | last post by:
I think I'm seeing table-level lock contention in the following function when I have many different concurrent callers, each with mutually distinct values for $1. Is there a way to reimplement...
20
by: Mark Harrison | last post by:
So I have some data that I want to put into a table. If the row already exists (as defined by the primary key), I would like to update the row. Otherwise, I would like to insert the row. I've...
3
by: spartacus | last post by:
Hi, Does SQL support update to multiple rows where values coming from a sub-query? e.g insert into TABLE1 select column1, column2, column3 from TABLE2
5
by: MARIEDB2 | last post by:
Hello, I am struggling to build a query on a DB2 db wich does an update for multiple fields which are the result of a select query with where clause (using multiple tables in the condition). ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.