MySql UPDATE subquery?? or...

[Transition]

Hey all,

Having some problems trying to execute a code as the following..

UPDATE products, products_description
SET products_description.products_description_ebay =
(SELECT products_description_ebay
FROM products_description
WHERE products_id =570)
WHERE products.products_model LIKE '%12345%'

That query generates an error and i'm not quite sure. Basically what i'm trying to do is load a rather large HTML product description into certain products where their model numbers contain a certain string.

Any help would be appreciated.

[longneck]

and the error is...

[r937]

if i understand correctly, your SET statement should be trying to update a column of the products table, not the products_description table?

[Transition]

and the error is...

Check my syntax near (SELECT products_description_ebay
FROM products_description
WHERE products_id =570)

[Transition]

if i understand correctly, your SET statement should be trying to update a column of the products table, not the products_description table?

I'm trying to update a product description from the products_description table, but i need to cross reference the model number held in the products table

[r937]

still doesn't make sense, especially "i need to cross-reference the model number"

which table are you updating?

[longneck]

you are not running mysql 4.1 or higher. therefore you can not use subqueries. please try this query to confirm: select version();

[longneck]

i just noticed what rudy is getting at. you have no relationship defined between the two tables. so even if you were using mysql 4.1 or higher, i think you would end up updating every record in your table.

[Haleden]

Just a note, subqueries do appear to function in 4.0.13 (which is less than 4.1) in UPDATE statements.

[longneck]

Just a note, subqueries do appear to function in 4.0.13 (which is less than 4.1) in UPDATE statements.

i've never seen this. can you provide an example?

[r937]

longneck, you're looking at an example right in this thread

[longneck]

longneck, you're looking at an example right in this thread

a non-working example:

Check my syntax near (SELECT products_description_ebay
FROM products_description
WHERE products_id =570)

that's the error you get when you're using a verion of mysql that doesn't support subqueries. am i missing something?

[Transition]

Here's the exact message i'm receiving..

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT products_description_ebay
FROM products_description
WH

[Transition]

you are not running mysql 4.1 or higher. therefore you can not use subqueries. please try this query to confirm: select version();

MySQL 4.0.25-standard

[r937]

you know, i think the problem is that you can't use subqueries before version 4.1

okay, now that that's out of the way ...

could you please take a moment and examine the relationship between products and products_description, and let us know, because there is a way you can do a "joined update", but we don't really know what you're trying to update from where

[Transition]

you know, i think the problem is that you can't use subqueries before version 4.1

okay, now that that's out of the way ...

could you please take a moment and examine the relationship between products and products_description, and let us know, because there is a way you can do a "joined update", but we don't really know what you're trying to update from where

The relationship between the two tables is a unique field called the products_id field. What i'm trying to do is update a field in products_description where a field in the products table contains a certain model number. So.... If products.products_model LIKE '%12345%' then use that unique product id associated w/ that row to set a field in products_description to the same value as products_description.products_id = 123.

[r937]

sorry, i still don't get it

if products.products_model LIKE '%12345%' does not guarantee a unique product, it will probably select several products

okay, let's take each of these product ids, and do what? find the matching products_description row? does each product id correspond to exactly one and only one products_description row?

okay, you want to take the 12345 product ids (plural) and set some field -- which field? -- in the products_description row -- which row? -- to the same value as some field -- which field? -- of the products_description row with product_id 123?

i am so lost

perhaps you could show us some example rows in each table, before and after the update?