UPDATE with INNER JOIN SQL Server

try this, (r syntax looks fine for me

UPDATE         AliasA
SET                CarMaker = mstMakerModel.Maker
FROM            tblCarAuctionList AS AliasA INNER JOIN
                      mstMakerModel ON AliasA.CarName = mstMakerModel.Model

Are you updating a view ? or an identity column ?

Updating a table.

Is CarMaker a computed column?

Just a regular column.

This does not make sense.  If you have tested with Aneesh's comment and got the same error message than post the exact error message and structure of tblCarAuctionList and mstMakerModel

Have a look to see if there are any triggers on the table.

If it's a sql server error message there should be more to it. Can you post the full message.

No triggers.  Actually, the message is in Japanese, since that's where I'm working.

So the qustion remains:  Is it a SQL Server system error message?

>> I think you can not use a table alias with update statement.
Not true.

Actually, I solved this on my own.  Here's my solution:

UPDATE         tblCarAuctionList
SET                CarMaker = mstMakerModel.Maker
FROM            tblCarAuctionList INNER JOIN
                      mstMakerModel ON tblCarAuctionList.CarName = mstMakerModel.Model
WHERE           (tblCarAuctionList.AuctionSite = 'USS') AND
                      (tblCarAuctionList.CarMaker IS NULL OR
                      tblCarAuctionList.CarMaker = '')
AND (mstMakerModel.Maker IS NOT NULL);


Thanks for all your replies.
Regards,
Alan Wooley

Yeah, you removed the table alias as  I stated above ;)

you CAN use aliases in an update statement - just not on the SET items, which must be on their own with no tablename (as the set items have to be in the table referenced by the UPDATE clause)
(this was also the syntax originally posted by aneeshattingal...)

eg
UPDATE         tblCarAuctionList
SET                CarMaker = mstMakerModel.Maker
FROM            tblCarAuctionList  AliasA INNER JOIN
                      mstMakerModel AliasB ON AliasA.CarName = AliasB.Model
WHERE           (AliasA.AuctionSite = 'USS') AND
                      (AliasA.CarMaker IS NULL OR
                      AliasA.CarMaker = '')
AND (mstMakerModel.Maker IS NOT NULL);

It's the alias in the set statement that has fixed the problem as suggested in the first post in this thread.
The alias in the update is allowed - and necessary if the table is aliased multiple times. If it is only aliased once then you can use the alias or the table name.

DarthSonic,
>>Yeah, you removed the table alias as  I stated above<<
Than you had better take a second look. As Nigel has pointed Aneesh gave the correct solution in the first comment.

Try this
UPDATE test1 SET value = a.newval
FROM ( select t1.idcol, t1.value as newval
       from test1 as t1 INNER JOIN test2 ON t1.extid=test2.extid
       inner join t3 on t2.othercolumn = t3.somecolumn ) as a
where test1.idcol = a.idcol;

link to SQL Update Join

itcouple,

I hate to point out the obvious, but you do realize this question is closed and nearly 5 years old, right?