MySQL query UPDATE table A when elements from table B fulfill so

ghz 9months ago ⋅ 68 views
  • MySQL 8.0
  • Joomla 4.4.3
  • HikaShop 5.0.3

Hi, I'm developing an eCommerce website and I need to run an SQL command that update the category of all products that have a category beginning with 'HP '. There is a table PRODUCT with all products, a table CATEGORY with all categories and a table PRODUCT_CATEGORY that links product_id and category_id

The tables link are:

PRODUCT
product_id
icecat_category

PRODUCT_CATEGORY
category_id
product_id

CATEGORY
category_id
category_name

The Update i would like to perform is: search all the PRODUCT.product_id that have a CATEGORY.category_name LIKE 'HP %' and for this items modify the PRODUCT_CATEGORY.category_id and SET it to the CATEGORY.category_id WHERE CATEGORY.category_name = PRODUCT.icecat_category

I am sure it is more difficoult to explain rather to create the right SQL statement, i hope i'm been clear enough. Thank you very much Eiasu

Answers

To achieve the desired update in MySQL, you can use a combination of UPDATE and JOIN statements to update the PRODUCT_CATEGORY table based on conditions involving the PRODUCT and CATEGORY tables. Here's the SQL statement to accomplish this task:

UPDATE PRODUCT_CATEGORY AS pc
JOIN PRODUCT AS p ON pc.product_id = p.product_id
JOIN CATEGORY AS c ON p.icecat_category = c.category_name
SET pc.category_id = c.category_id
WHERE c.category_name LIKE 'HP %';

Let's break down this SQL statement:

  1. UPDATE PRODUCT_CATEGORY AS pc: Specifies the table to update and assigns an alias pc for it.

  2. JOIN PRODUCT AS p ON pc.product_id = p.product_id: Joins the PRODUCT_CATEGORY table with the PRODUCT table based on the product_id column to get product details.

  3. JOIN CATEGORY AS c ON p.icecat_category = c.category_name: Joins the PRODUCT table with the CATEGORY table based on the icecat_category column to get the category details.

  4. SET pc.category_id = c.category_id: Sets the category_id column in the PRODUCT_CATEGORY table to the category_id column in the CATEGORY table.

  5. WHERE c.category_name LIKE 'HP %': Filters the rows where the category name starts with 'HP '.

This SQL statement will update the PRODUCT_CATEGORY table for all products whose icecat category matches a category name starting with 'HP '.

#mysql