Basic SQL Question - 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)

Basic SQL Question

Author

Topic

squab
Starting Member

15 Posts

Posted - 2004-05-22 : 12:42:55

Hello,

Anybody could help me please. I have 2 tables : A and B (see screenshot below)

How I can update column DeviceId from table A with data from table B (A.netAdressId = B.netAdressId) ?

Thanks in advance for your help.

Regards,

SqUaB

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-22 : 13:03:56

UPDATE ta
SET ta.deviceId = tb.deviceId
FROM
TableA ta
INNER JOIN TableB tb ON ta.netAddressId = tb.netAddressId

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

squab
Starting Member

15 Posts

Posted - 2004-05-22 : 17:24:32

Thanks Derrick, but it doesn't work :-(

ta = BDDSPSRVNATNV..NetAddresses_Copy
tb = BDDCTSRVNATNV91..deviceNetAddrRels

UPDATE NetAddresses_Copy
SET N.deviceId = D.deviceId
FROM BDDSPSRVNATNV..NetAddresses_Copy N
INNER JOIN BDDCTSRVNATNV91..deviceNetAddrRels D ON N.netAddressId = D.netAddressId

Server: Msg 1032, Level 15, State 1, Procedure CreateTableNetAddresses_Copy, Line 51
Cannot use the column prefix 'N'. This must match the object in the UPDATE clause 'NetAddresses_Copy'.

Do you understand why ?

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-05-22 : 17:57:07

Try chaning it from

UPDATE NetAddresses_Copy
SET N.deviceId = D.deviceId
FROM BDDSPSRVNATNV..NetAddresses_Copy N
INNER JOIN BDDCTSRVNATNV91..deviceNetAddrRels D ON N.netAddressId = D.netAddressId

To

UPDATE N
SET N.deviceId = D.deviceId
FROM BDDSPSRVNATNV..NetAddresses_Copy N
INNER JOIN BDDCTSRVNATNV91..deviceNetAddrRels D ON N.netAddressId = D.netAddressId

The reason being that your including a from in your UPDATE statement so you need to update with your table alias.

Dustin Michaels

squab
Starting Member

15 Posts

Posted - 2004-05-22 : 18:35:03

Many Thanks to all of you ! it's working fine !

Best Regards,

SqUaB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources