Posted by Hugo Kornelis on 02/27/06 23:36

On 27 Feb 2006 12:22:26 -0800, solidsna2@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

[Back to original message]