Error using Update Join statement from a Table Variable

Question

If I make a table variable here:

declare @Table Table (ProductID int, Color = varchar(60))

Then populate it of course, and try to use it in an Update with Join statement like below, I get errors.

UPDATE [Product]
    SET [Product].[Active] = 1                      
    ,[Product].[Color] = t.[Color]
    INNER JOIN @Table t 
    ON t.[ProductID] = [Product].[ProductID]

Error:

 Msg 156, Level 15, State 1, Procedure
 Incorrect syntax near the keyword 'INNER'.

Any suggestions how to do this?

Do you ever read the documentation? http://msdn.microsoft.com/en-us/library/ms177523.aspx — Aaron Bertrand, Apr 26 '12 at 03:22
@AaronBertrand Thank you for your answer, it solved my issue.. Too bad you removed it i was about to mark it as best. — Control Freak, Apr 26 '12 at 03:24
I just discovered this script which I am going to implement. You annoyed the crap out of me this week, and I'm horrible at checking the username before I answer. http://meta.stackexchange.com/questions/3360/ignore-users-script — Aaron Bertrand, Apr 26 '12 at 03:25
Sorry if i made it harder than usual for you to earn your points. — Control Freak, Apr 26 '12 at 03:28
I don't give a crap about the points. i care about wasting my time on people who don't value others' time. You need to learn how to ask questions and provide information when asked. Please give this a REALLY GOOD READ: http://www.codinghorror.com/blog/2012/03/rubber-duck-problem-solving.html and this one too: http://superuser.com/questions/how-to-ask — Aaron Bertrand, Apr 26 '12 at 03:31
PS yes it was me who down-voted this one. Reason: not RTFMing. — Aaron Bertrand, Apr 26 '12 at 03:37
Thank you again Aaron, You never fail to amaze me with your generosity. — Control Freak, Apr 26 '12 at 03:43
possible duplicate of [Update a table using JOIN in SQL Server?](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) — Aaron Bertrand, Apr 26 '12 at 03:53

Michael Buen · Accepted Answer · 2012-04-26T03:58:47.360

You can also do this (works on SQL Server and all ANSI SQL conforming database):

UPDATE [Product] SET 
    [Product].[Active] = 1                      
    ,[Product].[Color] = t.[Color]
FROM @Table t 
WHERE t.[ProductID] = [Product].[ProductID]

There's something to be desired from SQL Server's proprietary UPDATE FROM though, you can easily change the UPDATE statement to make it match whole table regardless if there's no matching rows.

It's easy to fashion this matched-rows-only update... http://www.sqlfiddle.com/#!3/8b5a3/26

update p SET
  Qty = t.Qty
from Product p
inner join Latest t 
on t.ProductId = p.ProductId;

...to an UPDATE that matches all rows, you merely change the INNER JOIN to LEFT JOIN: http://www.sqlfiddle.com/#!3/8b5a3/27

update p SET
  Qty = ISNULL(t.Qty,0)
from Product p
left join Latest t 
on t.ProductId = p.ProductId;


select * from Product;

Whereas if you want to fashion the ANSI SQL UPDATE with matched-rows-only ... http://www.sqlfiddle.com/#!3/8b5a3/28

update Product SET
  Qty = t.Qty
from Latest t
where t.ProductId = Product.ProductId

...to UPDATE statement that matches all rows, you have to adjust your query a bit: http://www.sqlfiddle.com/#!3/8b5a3/29

update Product SET
  Qty = ISNULL(t.Qty, 0)
from 
(
  select x.ProductId, lat.Qty
  from Product x
  left join Latest lat on lat.ProductId = x.ProductId
) as t  
where t.ProductId = Product.ProductId;

Though as most choices in development, one should weigh the pros and cons in terms of code readability/maintainability against flexibility

Data sample:

create table Product
(
  ProductId int primary key not null,
  Name varchar(50) not null,
  Qty int not null
  );


insert into Product(Name,Qty) values
(1,'CAR',1),
(2,'Computer',1000),
(3,'Shoes',2);


create table Latest
(
  ProductId int primary key not null,
  Qty int not null
);

insert into Latest(ProductId, Qty) values
(2,2000),
(3,3);

+1. A little cleaner than the proprietary UPDATE...FROM that I always tend to use. — Aaron Bertrand, Apr 26 '12 at 03:38

Error using Update Join statement from a Table Variable

1 Answers1