Posted by Erland Sommarskog on 02/28/06 00:48

(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: sqlservr.exe memory usage
• Prev in forum: Re: torn page detection and auto shrink: performance?
• Thread view: Re: Error using UPDATE statement

[Reply to this message]