mysql_query ON DUPLICATE KEY UPDATE

Question

I have problem, I would like to make update when hash is same. hash is unique at table. I cant find the right way to do this.

mysql_query("INSERT INTO shop_product_search3_muokattu2 
            (id, hash, product_name, type, manufacturer, mini_products) VALUES
            (NULL, '".$hash."', '".$row['product_name']."', '".$row['type']."', '". $row['manufacturer']."', '".$row['mini_products']."') 
            ON DUPLICATE KEY 
               UPDATE mini_products += VALUES('".$mini_products."') ") 
            or die(mysql_error());

When I try to add new $row['mini_products'] to behind of old $row['mini_products'] when hash matches. I dont know to update database like that. I just made it to add new to replace old data, but I want to make them add together. mini_products = '".$row['mini_products']."' ") or die(mysql_error()); — Marko Ylitalo
– Marko Ylitalo, Commented Sep 5, 2014 at 11:13
Obligatory: If this is PHP (which it looks like), mysql_query is deprecated php.net/manual/en/function.mysql-query.php — Jaydee
– Jaydee, Commented Sep 5, 2014 at 12:00

Gordon Linoff · Accepted Answer · 2014-09-05 11:53:02Z

1

This is your query:

INSERT INTO shop_product_search3_muokattu2(id, hash, product_name, type, manufacturer, mini_products) 
    VALUES (NULL, '".$hash."', '".$row['product_name']."', '".$row['type']."', '". $row['manufacturer']."', '".$row['mini_products']."') 
    ON DUPLICATE KEY UPDATE mini_products += VALUES('".$mini_products."'

I believe you have a problem with the on duplicate key update statement. The argument to VALUES should be a column name. Alternatively, you can put the value in directly. So, either of the following should work:

    ON DUPLICATE KEY UPDATE mini_products += VALUES(mini_products)
    ON DUPLICATE KEY UPDATE mini_products += $mini_products

As a note: I assume that += works, but I would write these as:

    ON DUPLICATE KEY UPDATE mini_products = coalesce(mini_products, 0) + VALUES(mini_products)
    ON DUPLICATE KEY UPDATE mini_products = coalesce(mini_products, 0) + $mini_products

edited Sep 5, 2014 at 11:53

answered Sep 5, 2014 at 11:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marko Ylitalo Over a year ago

ON DUPLICATE KEY UPDATE mini_products = mini_products + '".$row['mini_products']."' ") or die(mysql_error()); when I try add them database only show me result of 0.

Marko Ylitalo Over a year ago

I dont have any clue how to make update statement. It should push new data with old to mini_products column. And I am writing PHP.

Collectives™ on Stack Overflow

mysql_query ON DUPLICATE KEY UPDATE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related