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... 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.
"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.
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.
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.
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! =-----
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! =-----
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! =-----
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! =-----
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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,
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
|
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).
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |