Friday, 23 August 2013

update if exit and add new mysql ..

update if exit and add new mysql ..

i got the following mysql query..I have tried many different format but
cant seem to get this to work. I got two tables. table mic.temp has three
columns while table products has quite a few.
I need to update values into table products from table mic.temp. The
matching column is model number.
i have written the following query but it updates all the field.I only
need to update the values found in temp table and also auto increment the
product table.if a value is not found then insert it.I don't mind if non
existent values in temp table are entered as null.
mysql_query('INSERT INTO products(products_id, products_quantity,
products_model, products_ean, products_image, products_price,
products_date_added, products_last_modified, products_date_available,
products_weight, products_status, products_tax_class_id, manufacturers_id,
products_ordered, products_last_import, icecat_prodid, vendors_id,
products_availability)
SELECT model, stock, price
FROM mic_temp
ON DUPLICATE KEY UPDATE set
products.products_quantity = mic_temp.stock,
products.products_price= mic_temp.price');

No comments:

Post a Comment