Update value based on value in another table

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)

Update value based on value in another table

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-12-16 : 06:44:00

Hi,
I've got these two tables (simple example)

Table1
Name Number
NULL 1
NULL 2
NULL 3

Table2
Name Number
Alpha 1
Beta 2
Gamma 3

I want to update the name in table1 with the name from table2 where the numbers match

How can I do that?

Thanks

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-16 : 06:48:32

update t1 set t1.name=t2.name from Table1 t1 inner join Table2 t2 on
t1.number=t2.number

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-12-16 : 06:56:01

Thanks!

This is the actual SQL

UPDATE dbPubMatrix..tblLoRenewals SET dbPubMatrix..tblLoRenewals.salesperson = EMATRIXBCK..tblLoRenewals.salesperson
FROM dbPubMatrix..tblLoRenewals INNER JOIN EMATRIXBCK..tblLoRenewals ON
dbPubMatrix..tblLoRenewals.id = EMATRIXBCK..tblLoRenewals.id

I get this error

The objects "EMATRIXBCK..tblLoRenewals" and "dbPubMatrix..tblLoRenewals" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Can you help?

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 07:07:38

UPDATE t1
SET t1.salesperson = t2.salesperson
FROM dbPubMatrix..tblLoRenewals as t1 INNER JOIN EMATRIXBCK..tblLoRenewals as t2 ON
t1.id = t2.id

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources