UPDATE Statement Differences Between 2000 And 2005

I just wanted to post a difference I found between SQL 2000 and SQL 2005 regarding UDPATE statements that are done on a join. I understand that if tables are designed correctly this won't be a problem. But, when you inherit a bad design, you are unfortunately stuck with it. Hopefully this will help ease data differences in your migration from SQL 2000 to SQL 2005.



Run this code on a SQL 2000 connection, then run on SQL 2005. My guess on the behavior difference is strictly performance based since 2005 pulls the top result. Either way it can cause a lot of head scratching if you're not aware of it.



IF OBJECT_ID('tempdb..#UpdateTestA') IS NOT NULL

DROP TABLE #UpdateTestA



IF OBJECT_ID('tempdb..#UpdateTestB') IS NOT NULL

DROP TABLE #UpdateTestB



CREATE TABLE #UpdateTestA(

UpdateTestA int identity(1, 1),

FullName varchar(20),

UpdateData varchar(10))



CREATE TABLE #UpdateTestB(

UpdateTestB int identity(1, 1),

FullName varchar(20),

UpdateData varchar(10))



INSERT INTO #UpdateTestA(

FullName)

VALUES ('Barney Rubble')



INSERT INTO #UpdateTestB(

FullName,

UpdateData)

VALUES ('Barney Rubble', 'First')



INSERT INTO #UpdateTestB(

FullName,

UpdateData)

VALUES ('Barney Rubble', 'Second')



SELECT * FROM #UpdateTestA



UPDATE a

SET a.UpdateData = b.UpdateData

FROM #UpdateTestA a

INNER JOIN #UpdateTestB b on b.FullName = a.FullName



SELECT * FROM #UpdateTestA



DROP TABLE #UpdateTestA

DROP TABLE #UpdateTestB



Hope this solves a problem that you were having too.