Page 1 of 1

Populate Column from Joined Table

Posted: Wed Jun 13, 2012 11:37 am
by Jonah Bron
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:

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
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!

Re: Populate Column from Joined Table

Posted: Wed Jun 13, 2012 12:21 pm
by Christopher
The problem is your WHERE statement. You don't JOIN on the categories table so where does categories.categories_id come from?

Re: Populate Column from Joined Table

Posted: Wed Jun 13, 2012 12:27 pm
by Jonah Bron
It's at the top, it's the subject of the UPDATE statement. Doesn't that make it present?

Edit:
Oh, I see what you're saying. Exactly, that's the part I'm trying to work through. I want it to use the value from the current row in the UPDATE statement.

Re: Populate Column from Joined Table

Posted: Wed Jun 13, 2012 12:29 pm
by Jonah Bron
I found someone with a similar problem here:

http://stackoverflow.com/a/844637/278899

I tried to make an adaptation of it, but it affects no rows:

Code: Select all

UPDATE categories
INNER JOIN (
	SELECT products_to_categories.categories_id AS categories_id, products_to_categories.products_id AS products_id, products.products_image AS products_image
	FROM products
        INNER JOIN products_to_categories
        ON products.products_id  = products_to_categories.products_id
) AS categories_products
ON categories.categories_id =categories_products.categories_id
SET categories.categories_image = categories_products.products_image
The sub-query returns the expected rows.