Update - 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 2000 Forums

Transact-SQL (2000)

Update

Author

Topic

cblatchford
Starting Member

40 Posts

Posted - 2006-01-12 : 05:28:29

Hi chaps, just a simple one today (hopefully)..

I have table1 with columns "pkid", "location" and table2 with columns "pkid", "location". I want to update table2 with table1 "location" value where the "pkid" value matches in both tables; whats the best way to do this?

UPDATE table2
SET location = ( SELECT table1.location
FROM table1
WHERE table1.pkid = table2.pkid)

There are obviously multiple values in both tables, so at the end of the day if table1 has lots of updates, I need the query to replace the "location" field within table2 with table1 values where the "pkid" matches for each row.

Thanks..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 05:41:26

Update t2
set t2.location=t1.location
from table2 t2 inner join table1 t1
on t2.pkid=t1.pkid

Madhivanan

Failing to plan is Planning to fail

cblatchford
Starting Member

40 Posts

Posted - 2006-01-12 : 05:55:33

Thats great thanks..

Following on from this, how do I select the most recent update from table1? For example, if there were 3 updates in table1 with the same pkid, obviously I would want to select the most recent update, is there a way of doing this?

Antonio
Posting Yak Master

168 Posts

Posted - 2006-01-12 : 06:26:23

quote:
Originally posted by cblatchford

Thats great thanks..

Following on from this, how do I select the most recent update from table1? For example, if there were 3 updates in table1 with the same pkid, obviously I would want to select the most recent update, is there a way of doing this?

Created an additional time stamp field in your table for records that get updated. For example :


update someTable
set someColumn = something,
    updateTime = getdate()
where someCondition = someOtherCondition

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 07:05:19

Something like

Select columns from yourTable
where <condition> Group by columns
having datecol=max(datecol)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources