473,325 Members | 2,785 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,325 software developers and data experts.

Error using UPDATE statement

Hi,

I am relatively new to SQL. I am using SQL 2000. I am trying to
Update a field base in a criteria in a scond table.

UPDATE Tbl1
SET Tbl1.Row2 = '1'
WHERE Tbl1.Row1 =
(SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3))

Row 1 is the key between the two table. If I am doing only the select
below, I am getting the right value.

SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3)

When I am running the entire querry, I am getting this error:

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.

What I am trying to do is to update a field in Tbl1 base on a date in
Tbl2. If the date is expire, I want to raise a flag, in Tbl1.

Thank you

Philippe

Feb 27 '06 #1
4 1902
On 27 Feb 2006 12:22:26 -0800, so*******@gmail.com wrote:
Hi,

I am relatively new to SQL. I am using SQL 2000. I am trying to
Update a field base in a criteria in a scond table.

UPDATE Tbl1
SET Tbl1.Row2 = '1'
WHERE Tbl1.Row1 =
(SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3))

Row 1 is the key between the two table. If I am doing only the select
below, I am getting the right value.

SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3)

When I am running the entire querry, I am getting this error:

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.

What I am trying to do is to update a field in Tbl1 base on a date in
Tbl2. If the date is expire, I want to raise a flag, in Tbl1.

Thank you

Philippe


Hi Philippe,

Hard to be sure without CREATE TABLE statements, INSERT statements and
expected output (see www.aspfaq.com/5006), but I guess that you need
something like this:

UPDATE Tbl1
SET Row2 = '1'
WHERE EXISTS
(SELECT *
FROM Tbl2
WHERE Tbl2.Row3 >= CURRENT_TIMESTAMP
AND Tbl2.Row1 = Tbl1.Row1)

--
Hugo Kornelis, SQL Server MVP
Feb 27 '06 #2
(so*******@gmail.com) writes:
I am relatively new to SQL. I am using SQL 2000. I am trying to
Update a field base in a criteria in a scond table.

UPDATE Tbl1
SET Tbl1.Row2 = '1'
WHERE Tbl1.Row1 =
(SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3))


This does not look right. You have Tbl1 once extra in the subquery,
making it entirely uncorrelated with the outer Tbl1. Try chaning the
query to:

UPDATE Tbl1
SET Row2 = '1'
WHERE Tbl1.Row1 = (SELECT Tbl2.Row1
FROM Tbl2
WHERE Tbl2.Row1 = Tbl1.Row1
AND CURRENT_TIMESTAMP >= Tbl2.Row3)

I also changed {fn current_timestamp() } as there is no reason to
call an ODBC function to get the current date.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 27 '06 #3
Thank you for the reply. Sadly, I am getting the same error with your
solution. If I run only the SELECT subquery alone, I am getting
mutiple result, and the result are what I am expecting. In Tbl2, Row1
can be the same for up to 8 lines. Row1 is the key between the 2
tables. In Tbl1, Row1 is unique.

Ex:
ROW1 ROW2 ROW3
1 xxx zzz
1 aaa bbb
1 ccc ddd
1 eee fff
1 ggg hhh

Maybe this is why it is giving me the error that I have mutiple value.
Like I sayd, I am a new to doing SQL programming. Maybe I am missing
something or I do not approach the problem properly.

Thank you again

Feb 28 '06 #4
Well I figure out the problem. I had a GROUP BY at the end of the
SELECT subquery.

UPDATE Tbl1
SET Row2 = '1'
WHERE Tbl1.Row1 = (SELECT Tbl2.Row1
FROM Tbl2
WHERE Tbl2.Row1 = Tbl1.Row1
AND CURRENT_TIMESTAMP >= Tbl2.Row3 GROUP BY
Tbl2.Row1)

Thank you again for your help.

Feb 28 '06 #5

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

Similar topics

3
by: Mohammed Mazid | last post by:
Can anyone please help me here? Basically I have modified the source code and understood it but when I update a record in the db using a JSP, it gives me an error "The flight you selected does...
7
by: Jack | last post by:
Hi, I am trying to test a sql statement in Access which gives me the error as stated in the heading. The sql statement is built as a part of asp login verification, where the userid and password...
33
by: coosa | last post by:
I have a table: ---------------------------------------------------- CREATE TABLE CATEGORY ( CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL, CATEGORY_NAME VARCHAR(40) NOT NULL,...
3
by: Nathan Bloomfield | last post by:
Hi there, I am having difficulty with a piece of code which would work wonders for my application if only the error trapping worked properly. Basically, it works as follows: - adds records...
4
by: Zeng | last post by:
Hello, I'm wondering if anybody has seen this problem. I basically need to cycle through ~30000 db rows to update the data, I load up the id of the rows I need first, put them into ArrayList,...
1
by: amitbadgi | last post by:
HI i am getting the foll error while conv an asp application to asp.net Exception Details: System.Runtime.InteropServices.COMException: Syntax error in UPDATE statement. Source Error: Line...
1
by: Jeremy Ames | last post by:
I have a datagrid that updates the table using a stored procedure. The stored procedure is confirmed to complete correctly, yet the sql data adapter is returning an error that my application is...
19
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without...
0
by: gshawn3 | last post by:
Hi, I am having a hard time creating a Trigger to update an Oracle database. I am using a SQL Server 2005 Express database on a Win XP Pro SP2 desktop, linked to an Oracle 10g database on a...
2
by: vasilip | last post by:
I am trying to use the XMLUPDATE stored procedure. Installed fine, and seems to work great if I execute the statement from a sql query using the controll center.. Doesn't work from java however. I...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
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.