Query For Updating Tables - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Query For Updating Tables

Author

Topic

insideMyCity
Starting Member

2 Posts

Posted - 2012-06-01 : 17:02:47

I have a queried results table where I need to take these results and update an individual row in two tables. For Example, Each table has a record that needs to be updated, each record has a field called InserviceID which is unique to each record. From the results table I am running an UPDATE WHERE statement saying if results.InserviceID = Table1.InserviceID then update the record with the new information.

My problem is that even though the record is being updated, all of the other records in Table 1 are being changed to 'Null' value in that field.

Any thoughts??

Scott Aschenbrenner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-01 : 17:49:19

Show us your query and some sample data. It's hard to picture what you mean with just your description.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 23:46:04

quote:
Originally posted by insideMyCity

I have a queried results table where I need to take these results and update an individual row in two tables. For Example, Each table has a record that needs to be updated, each record has a field called InserviceID which is unique to each record. From the results table I am running an UPDATE WHERE statement saying if results.InserviceID = Table1.InserviceID then update the record with the new information.

My problem is that even though the record is being updated, all of the other records in Table 1 are being changed to 'Null' value in that field.

Any thoughts??

Scott Aschenbrenner

actually you should not be adding this to where but you should be joining on it

like


UPDATE t
SET t.Field = r.Field
FROM yourTable t
JOIN Results r 
ON r.InserviceID = t.InserviceID

this will make sure only records which are related to results will be affected by UPDATE operation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources