# Solved: MySQL update not working properly - what is wrong in my SQL statment?



## mtlhd (Oct 29, 2007)

Hey all I am working on a nightmare update.
I am trying to update a MySQL database for an opencart site from a MAS90 company database.

well, i created a table in MySQL for my CSV from MAS90

so over all, I am trying to update a quantity in one table on the site, that takes me calling 2 other tables for ids so I can pull the new quantity from my new update table.

here is the SQL statement I conjured up -


```
UPDATE 
product_option_value, product_option_value_description, product, product_update
SET 
product_option_value.quantity = product_update.quantity
WHERE 
product_update.product_id = product_option_value.product_id 
AND 
product_update.product_id = product_option_value_description.product_id
AND
product_option_value_description.name = product_update.size;
```
Unforuntately, what happens is, it only pulls from one of the sizes, and deploys that to all the sizes in the value table instead of updating its corresponding sizes.

Where am I going wrong in that statement?
Thanks in advance.


----------



## maneetpuri (Oct 14, 2008)

Hi,

I dont think you will be able to achieve this with one SQL stamen, you will have to write a function or a routine that will do this.

Cheers,

~Maneet


----------



## mtlhd (Oct 29, 2007)

Believe it or not, I got it to work.

Here is the final statement.

```
UPDATE 
product_option_value, product_option_value_description, product, product_update
SET 
product_option_value.quantity = product_update.quantity
WHERE 
product_update.product_id = product_option_value.product_id 
AND 
product_update.product_id = product_option_value_description.product_id
AND
product_option_value.product_option_value_id = product_option_value_description.product_option_value_id
AND
product_option_value_description.name = product_update.size;
```
The missing link was this:

product_option_value.product_option_value_id = product_option_value_description.product_option_value_id

When I made the option tables match each other's ids as well, it worked fine.


----------

