Populate Column from Joined Table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Populate Column from Joined Table

Post 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!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Populate Column from Joined Table

Post by Christopher »

The problem is your WHERE statement. You don't JOIN on the categories table so where does categories.categories_id come from?
(#10850)
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Populate Column from Joined Table

Post 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.
Last edited by Jonah Bron on Wed Jun 13, 2012 12:33 pm, edited 1 time in total.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Populate Column from Joined Table

Post 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.
Post Reply