UPDATE affecting unwanted rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UPDATE affecting unwanted rows

Author

Topic

SonoItaliano
Starting Member

2 Posts

Posted - 2012-09-05 : 09:18:37

Assume table PART with the following data:


    ID      QTY
     A      100
     B       76
     C      982
     D      318
     E      123
    ...     ...

and table TEMP with identical columns and only partial data, for example:


    ID      QTY
     B      625
     D      118
     E       23
    ...     ...

If I run the following query:


   update PART set QTY = (select QTY from TEMP where PART.ID = TEMP.ID)

Rows B, D, and E are correctly set to the new values, however rows A and C get nulled out. How do I avoid this?

I thought that it might be as simple as revising the query to reverse the where = comparison (ie: where TEMP.ID = PART.ID) but that did not help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-05 : 09:27:03

The inner join will take only rows with a matching row in table TEMP:

update p
set QTY = t.QTY
from PART as p
inner join TEMP as t on t.ID = p.ID

Too old to Rock'n'Roll too young to die.

SonoItaliano
Starting Member

2 Posts

Posted - 2012-09-05 : 09:32:46

quote:
Originally posted by webfred

The inner join will take only rows with a matching row in table TEMP:
...

Thanks, webfred.

It looks like I can also solve the problem by adding a WHERE EXISTS statement, thus:


  update PART set QTY = (select QTY from TEMP where PART.ID = TEMP.ID) where exists (select QTY from TEMP where PART.ID = TEMP.ID)

Do you concur?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-09-05 : 19:03:28

Yes, but why would you? Webfred's solution is much better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources