update multiple records two tables

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 multiple records two tables

Author

Topic

jgnguyen
Starting Member

2 Posts

Posted - 2007-02-23 : 13:29:25

I need to update multiple records from one table with data from another table. this is what I have done but not sure if it's the most efficient way.

senerio, get changed value from tempTable and update all record to masterTable

tempTable
id fname lname add1
1 john doe one st.
2 jim doet 2 d st.
3 mary jane 3 st

masterTable
1 john doe 8 d st
2 jim doet 33 e st
3 mary clark 3 st

declare @count int
set @ count=0
while @count < (select count(*) from tempTable)
begin
set @count = @count + 1
update masterTable set fname = (select fname from tempTable where masterTable.id = tempTable.id), lname = (select lname from tempTable where masterTable.id = tempTable.id), add1 = (select add1 from tempTable where masterTable.id = tempTable.id)

end

is this the best way to do this?? can i declare a @where varialbe set it to @where = 'where masterTable.id = tempTable.id' and use the @where instead of having to type out everything.

thanks in advanced

one more thing.. any idea how to error check..let say something goes wrong and it fails halfway thru a row or something what can i do to log it etc..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 20:30:13

[code]
update m
set fname = t.fname,
lname = t.lname,
addr1 = t.addr1
from masterTable m inner join tempTable t
on m.id = t.id
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources