Incorrect syntax in Inner Join update statment 1

[gus121]

Hi i wanted to find away to do an update using a Join between three tables and i was strugelling until i stumbled accross this in the frequently asked questions. faq183-1958.

However now i get a syntax error when i try and verify the SQL. any ideas?

Incorrect syntax by the word AS.

UPDATE    tblCategories AS tblParentCat INNER JOIN
                      tblCategories AS tblSubCat ON tblParentCat.Category_ID = tblSubCat.SubCategory_ID INNER JOIN
                      tblProducts ON tblSubCat.Category_ID = tblProducts.Category_ID
SET              tblParentCat.Mask = 15, tblSubCat.Mask + 0, tblProducts.Mask + 0
WHERE     (tblParentCat.SubCategory_ID = 0) AND (tblParentCat.Category_ID = 1)

thanks

-Gus

 

[JamesLean]

1) You can only update one table at a time.
2) If you want to join multiple tables you need to use a FROM clause.

Try this to start:

UPDATE cat
SET mask = 15
FROM tblCategories cat
  JOIN tblCategories subcat ON cat.category_id = subcat.subcategory_id
  JOIN tblProducts p ON subcat.category_id = p.category_id
WHERE cat.subcategory_id = 0
  AND cat.category_id = 1

You would then have to use the same query but just change the table name in the UPDATE clause to update the other two tables.

--James

 

[gus121]

Hi James thanks for your help

The below code works fine. However on the second and third update statment I wish to SUM a value with the existing mask value is there an easy way to this?

UPDATE cat
SET mask = @mask
FROM tblCategories AS cat
WHERE cat.subcategory_id = 0
  AND cat.category_id = @CategoryID

UPDATE subcat
SET mask = @mask /* mask + @mask */
FROM tblCategories AS cat
  JOIN tblCategories AS subcat ON cat.category_id = subcat.subcategory_id
WHERE cat.subcategory_id = 0
  AND cat.category_id = @CategoryID

UPDATE p
SET mask = @Mask /* mask + @mask */
FROM tblCategories AS cat
  JOIN tblCategories AS subcat ON cat.category_id = subcat.subcategory_id
  JOIN tblProducts AS p ON subcat.category_id = p.category_id
WHERE cat.subcategory_id = 0
  AND cat.category_id = @CategoryID

thanks


-Gus

 

[JamesLean]

You have it right!

UPDATE subcat
SET mask = mask + @mask
...

--James

 

[gus121]

Thanks James for your help.

-Gus