Help with a cross server update statement

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

General SQL Server Forums

New to SQL Server Programming

Help with a cross server update statement

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-04-14 : 17:02:41

I'm so bad with these. I have two tables on two linked servers. I need to write a statement to update multiple rows in one table to the corresponding rows in the other table. Here is my attempt:


update [nintgisqlcl01].[pdp].[dbo].[groups]
set group_name = (	select group_name 
				from [dbo].[groups]
				where language='ru' and sid=67732)
where sid = '67732'

I understand why that won't work. That would update all the values in the target with one entry. Each row has a distinct entry. I don't want to navigate through each row one at a time in the where statements either. Is a join the way to do this? Or a loop of some kind? Help! I really need to learn this fast!
Thanks

Craig Greenwood

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-14 : 17:09:18

[CODE]
update table1
set group_name = b.group_name
from table1 a
join table2 b
on a.siid = b.siid and b.language = 'ru'[/CODE]

quote:
Originally posted by craigwg

I'm so bad with these. I have two tables on two linked servers. I need to write a statement to update multiple rows in one table to the corresponding rows in the other table. Here is my attempt:

update [nintgisqlcl01].[pdp].[dbo].[groups]
set group_name = ( select group_name
from [dbo].[groups]
where language='ru' and sid=67732)
where sid = '67732'

I understand why that won't work. That would update all the values in the target with one entry. Each row has a distinct entry. I don't want to navigate through each row one at a time in the where statements either. Is a join the way to do this? Or a loop of some kind? Help! I really need to learn this fast!
Thanks

Craig Greenwood

craigwg
Posting Yak Master

154 Posts

Posted - 2010-04-15 : 09:48:03

Thanks, this got me headed the right direction. One of my key issues was learning that I must join on all keys in the table (using a few composite primary keys). Here was the statement I actually ran:


update [nintgisqlcl01].[pdp].[dbo].[groups]
set description = a.description
from groups a 
join [nintgisqlcl01].[pdp].[dbo].[groups] b 
on a.sid = b.sid and a.gid=b.gid and a.language = b.language

Thanks for the help!

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources