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

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  ({ f
n
>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  ({ f
n
>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

(solidsna2@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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