468,554 Members | 1,910 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,554 developers. It's quick & easy.

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 1727
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Mohammed Mazid | last post: by
33 posts views Thread by coosa | last post: by
1 post views Thread by amitbadgi | last post: by
1 post views Thread by Jeremy Ames | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.