Match and update records from one table to another

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)

Match and update records from one table to another

Author

Topic

nonsec
Starting Member

26 Posts

Posted - 2009-06-23 : 15:38:36

Hello everyone,

I have 2 table table structures are

first table called "rawdata" one;



 ServerName		SITENAME
server25.pac.lcl	not assigned
servernj.pac.lcl	not assigned
servernj.pac.lcl	not assigned
servernj.pac.lcl	not assigned
server45.pac.lcl	not assigned
server25.pac.lcl	not assigned
servernj.pac.lcl	not assigned
servernj.pac.lcl	not assigned
servernj.pac.lcl	not assigned
server45.pac.lcl	not assigned

Second table called "ServerDetails"


 ServerName		SITENAME
server25.pac.lcl	ABC
servernj.pac.lcl	Remote
servernj.pac.lcl	Remote
servernj.pac.lcl	Remote
server45.pac.lcl	Local

Most of the records in the first table are repeated records kind of duplicate.
On the second table "Servername" field is DISTINCT copy of "ServerName" field in the first table.

I want to update SiteName field from second table to First Table but ServerName has to be matched with first table.

I hope i explained well. How can I achieve this?

Thank you.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-23 : 15:58:14

update A
set A.SITENAME = B.SITENAME
from rawdata A
inner join ServerDetails B on A.ServerName = B.ServerName

nonsec
Starting Member

26 Posts

Posted - 2009-06-23 : 16:29:17

vijayisonly,
Thank you so much. One more thing what if the SERVERNAME not match, can I update that field something like "Server name not matched" ?

Thank you again.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-23 : 16:57:28

quote:
Originally posted by nonsec

vijayisonly,
Thank you so much. One more thing what if the SERVERNAME not match, can I update that field something like "Server name not matched" ?

Thank you again.

Yes. You can...code needs to change to

update A
set A.SITENAME = coalesce(B.SITENAME,'Server name not matched')
from rawdata A
left join ServerDetails B on A.ServerName = B.ServerName

nonsec
Starting Member

26 Posts

Posted - 2009-06-29 : 12:10:13

Thank you very much, It worked flawlessly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources