Populate Column from Joined Table
Posted: Wed Jun 13, 2012 11:37 am
Hello, world!
I'm working with osCommerce, specifically three of it's tables: categories, products, and products_to_categories. As you can imagine, categories and products are linked by products_to_categories. I need to grab one of the columns from the products table (products_image), and put it into the categories table. This is what I have so far:
But apparently the sub-query returns NULL. Now, I think everything about the SQL statement is correct, except the WHERE clause of the sub-query. I don't know how to get a column value from the current row of the surrounding UPDATE statement into the WHERE clause of the sub-query. Guidance? Thanks!
I'm working with osCommerce, specifically three of it's tables: categories, products, and products_to_categories. As you can imagine, categories and products are linked by products_to_categories. I need to grab one of the columns from the products table (products_image), and put it into the categories table. This is what I have so far:
Code: Select all
UPDATE categories
SET categories.categories_image = (
SELECT products.products_image
FROM products
LEFT OUTER JOIN products_to_categories
ON products.products_id = products_to_categories.products_id
WHERE products_to_categories.categories_id = categories.categories_id
LIMIT 1
)
WHERE categories.categories_image IS NULL