February 21, 2011 at 9:06 am
I have an update statement that returns a different value each time it runs. I'm not sure why. The clustered index which is also a primary key is on auto increment identity column, which is an integer data type. The update statement looks like this:
UPDATE a
SET a.flag2 = SIGN(a.flag + b.YAFlg)
from mytable a, mytable b
WHERE (b.datefield > a.datefield
AND a.numberfield = b.numberfield)
clustered index is on someidentity and not on the numberfield which happens to be a BIGINT data type.
Any thoughts?
February 21, 2011 at 10:39 am
WHERE (b.datefield > a.datefield
Doesn't this mean you might get varied results if more than one b.datefield qualifies as > than a.datefield ?
Why not use the JOIN syntax ?
February 21, 2011 at 10:55 am
homebrew01 (2/21/2011)
WHERE (b.datefield > a.datefieldDoesn't this mean you might get varied results if more than one b.datefield qualifies as > than a.datefield ?
Why not use the JOIN syntax ?
Why would the syntax make the difference? An "inner join" syntax is the same as "mytable a, mytable b where...."??
February 21, 2011 at 10:56 am
This is the problem when using UPDATE with a JOIN.
You have to ensure there is only a one to one relationship between the two tables. If there is a one to many relationship between the tables then only the last value will be updated and the last value will be non-deterministic. This is why some people want UPDATE with a JOIN depreciated in favor of the MERGE command. (MERGE will throw an exception if a one to many relationship exists.)
February 21, 2011 at 11:04 am
Thanks. I'm basically converting SQL statements to SQL server from mysql and making sure that all the data matches. I'm not sure what my answer to dev group should be...
February 21, 2011 at 11:10 am
Without some sample data and DDL it is difficult to say.
You need to do something to ensure that only a one to one relationship exists.
February 21, 2011 at 11:58 am
Luk (2/21/2011)
homebrew01 (2/21/2011)
WHERE (b.datefield > a.datefieldDoesn't this mean you might get varied results if more than one b.datefield qualifies as > than a.datefield ?
Why not use the JOIN syntax ?
Why would the syntax make the difference? An "inner join" syntax is the same as "mytable a, mytable b where...."??
Easier & more intuitive to read, that's all.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply