CODEWITHSUNDEEP
mysqlsqlsql-updateinner-join
Schtrudahl
Schtrudahl

Reputation: 25

UPDATE from two INNER JOINS

I want to update a column in table INVENTAR from a couple of INNER JOINS. I have the following:
Table INVENTAR with the column PRODUCT_ID,CATEGORY
Table PRODUCT_TO_CATEGORY with the columns PRODUCT_ID,CATEGORY_ID
Table CATEGORY_DESCRIPTION with the columns CATEGORY_ID, NAME

I want the NAME column to update the CATEGORY column. Here's my code:

UPDATE inventar
   SET inventar.category=category_description.name
  FROM inventar
       INNER JOIN product_to_category 
             ON product_to_category.product_id=inventar.product_id
       INNER JOIN category_description 
             ON category_description.category_id=product_to_category.category_id

Upvotes: 0

Views: 596

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The correct MySQL syntax is:

UPDATE inventar i INNER JOIN
       product_to_category ptc
       ON ptc.product_id = i.product_id INNER JOIN
       category_description cd
       ON cd.category_id = ptc.category_id
   SET i.category = cd.name;

Your syntax looks more appropriate for SQL Server or Postgres.

Upvotes: 3

Related Questions