Friday, March 9, 2012

Help - Updating A Field In Query Analyzer

Hi All,

I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code:

USE DBMyDatabase

UPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
WHERE IDField=TblOther.IDOld

SELECT Pk, IDField
FROM TblToBeUpdated

What am I doing wrong? The error code I get is:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.

Thanks.

HenryUPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
FROM TblToBeUpdated
inner join TblOther on TblToBeUpdated.IDField=TblOther.IDOld|||I very rarely use this syntax, but as I remember it:UPDATE tblToBeUpdated
SET IDField = b.IDNew
FROM tblToBeUpdated
JOIN tblOther AS b
ON (b.IDOld = tblToBeUpdated.IDField) should do the trick!

-PatP|||Thanks. Works beautifully.

Henry

No comments:

Post a Comment