|
|
Posted by Roy Harvey on 06/27/07 00:15
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.
That happens if there are rows in the table being UPDATEd that do not
have matches in the subquery. The FROM/JOIN prevents that in the
original versions. I don't know if any such non-matching rows exist,
but it certainly seems possible with the date range and type tests
dropping rows from the subquery.
I believe this query would show if the original UPDATEs using FROM
result in the same row updated more than once, without possible impact
on the data.
SELECT A.PrimaryKey, count(*)
FROM A
JOIN B
ON A.GUID1 = B.GUID1
JOIN C
ON B.GUID2 = B.GUID2
WHERE C.type = 1
AND @date BETWEEN B.DateFrom AND B.DateTo)
GROUP BY A.PrimaryKey
HAVING COUNT(*) > 1
Roy Harvey
Beacon Falls, CT
Navigation:
[Reply to this message]
|