Update Statement with Inner Join

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 Statement with Inner Join

Author

Topic

vicpal25
Starting Member

21 Posts

Posted - 2007-11-28 : 18:39:05

I have an UPDATE statement that is trying to update records from two different tables. I am getting an error that tells me:

'Cannot use the column prefix 'B'. This must match the object in the UPDATE clause 'A'.'

Does this mean I can only update tables from one table using the inner joined table?

Do I need to create two update statements? Here is my T-SQL

UPDATE A
SET A.Item_No = '2342-12',
A.Dsc = 'Testing Description',
B.Series_Ltr = 'B'
B.Rev_Ltr = NULL,
B.Begin_Eff_Dt = NULL,
B.End_Eff_Dt = NULL,
B.Build_UM = NULL,
B.Procurement = NULL,
B.Prepared_By = NULL,
B.Checked_By = NULL,
B.Release_Dt = NULL,
B.Remarks = NULL,
FROM ENGR_ITEM A
INNER JOIN Engr_BOM_Control B
ON A.pEngr_Item_ID=B.fEngr_Item_ID
where A.pEngr_Item_ID = '6040'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 18:50:39

Yes you need two update statements.

UPDATE A
SET Item_No = '2342-12',
Dsc = 'Testing Description',
FROM ENGR_ITEM A
INNER JOIN Engr_BOM_Control B
ON A.pEngr_Item_ID=B.fEngr_Item_ID
where A.pEngr_Item_ID = '6040'

UPDATE B
SET Series_Ltr = 'B'
Rev_Ltr = NULL,
Begin_Eff_Dt = NULL,
End_Eff_Dt = NULL,
Build_UM = NULL,
Procurement = NULL,
Prepared_By = NULL,
Checked_By = NULL,
Release_Dt = NULL,
Remarks = NULL,
FROM ENGR_ITEM A
INNER JOIN Engr_BOM_Control B
ON A.pEngr_Item_ID=B.fEngr_Item_ID
where A.pEngr_Item_ID = '6040'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

vicpal25
Starting Member

21 Posts

Posted - 2007-11-28 : 19:13:08

Sweet. Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources