I need an SQL statement please...

[♥mdtaylorlrim]

I have a category that has grown too large and I need to break it up before it gets out of hand. Takes too long to do it by hand already so I am looking for a sql statement that I can change the category on a subset or the products all at once.

 

Here is the select to get the products, I'm just not sure how the update statement looks...

 

SELECT p2c.products_id, pd.products_name
FROM `products_to_categories` p2c
INNER JOIN products_description pd ON p2c.products_id = pd.products_id
WHERE p2c.categories_id = "82"
AND pd.products_name LIKE "%powder%"

 

So, I need a statement that looks something like this...

 

UPDATE `products_to_categories` set categories_id = "177" where
(select pc.products_id from products_to_categories pc
INNER JOIN products_description pd ON products_id = pd.products_id
WHERE categories_id = "82"
AND pd.products_name LIKE "%powder%")

 

Can anyone confirm or correct the statement for me please?

[♥mdtaylorlrim]

This is what I finally used. It will update the selected column to whatever you want (literal) in one table based on a selection criteria from another table. Just in case anyone wants to use these please try them on a test db first so you know for sure it will work. I ain't perfect...

 

literal= what you want as the value in the column

column_name = existing column name is the database table

category_number = an existing category number

search_term = any string of characters

 

 

 

This one I used to bulk set a custom shipping method in the products table for all products in a specific category.

 

update products  p
set p.column_name = "literal"
WHERE  (

SELECT pc.products_id
FROM products_to_categories pc
WHERE p.products_id = pc.products_id and pc.categories_id = "category_number"
)

 

 

This one I used to change the category of products where the product name contained certain words.

update products_to_categories  pc
set pc.categories_id = "category_number"
WHERE  (

SELECT pd.products_name
FROM products_description pd
WHERE pd.products_name like "%search_term%"
)