Posted by Erland Sommarskog on 06/27/07 07:31

Roy Harvey (roy_harvey@snet.net) writes:
> On Tue, 26 Jun 2007 21:46:15 +0000 (UTC), Erland Sommarskog
><esquel@sommarskog.se> wrote:
>
>>One problem with UPDATE FROM is that you can update the same row
>>several times if your join conditions are not unique. What happens if
>>you run:
>>
>> UPDATE a
>> SET Field = (SELECT c.Field1
>> FROM c
>> JOIN b ON c.GUID2 = b.GUID2
>> WHERE a.GUID1 = b.GUID1
>> AND c.type = 1
>> AND @date BETWEEN b.DateFrom AND b.DateTo)
>
> BE CAREFUL WITH THIS!!
>
> One thing that could happen from this UPDATE is that it sets Field =
> NULL for rows that are untouched by the UPDATEs in the original post.

Right, Roy. I didn't add a WHERE clause, because it was more intended as a
test to see if the query would work at all. But I should have included the
warning.




--
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: sql Cannot resolve collation conflict equals - comparing rows and fileds between Table1 and View1
• Prev in forum: Re: Concat tables into one row in view
• Thread view: Re: Strange performance issue with UPDATE FROM

[Reply to this message]