Insert with on duplicate key update ignores index

Question

So I've got a table product_supplier that I need to add data to from import_tbl. Product_supplier has three columns product_id, supplier_id and price. Import_tbl has the same columns plus some extra. What's most important and what I can't get working is that when a specific combination of product_id and supplier_id exists, only the price should be updated. If that combination does not exist a new row needs to be added. I tried this query

INSERT INTO product_supplier (product_id, supplier_id, price)
SELECT i.product_id, i.supplier_id, i.price
FROM import_tbl i
ON DUPLICATE KEY UPDATE
    price = i.price

This one works if I add a row with a new product_id, but it totally ignores the supplier_id. So it won't add new rows if I a row uses the same product_id but a different supplier_id.

I think this has something to do with indexes, and I tried unique indexes for both product_id, and supplier_id and a multiple-column index of both product_id and supplier_id. But when I put EXPLAIN in front of the query it never recognises any indexes. Please some help, thanks!

Table structure of product_supplier

+---------------------+---------+------+-----+---------+----------------+
|        Field        |  Type   | Null | Key | Default |     Extra      |
+---------------------+---------+------+-----+---------+----------------+
| product_supplier_id | int(11) | NO   | PRI | NULL    | auto_increment |
| product_id          | int(11) | NO   | UNI | 0       |                |
| supplier_id         | int(11) | NO   | MUL | 0       |                |
| price               | int(11) | NO   |     | 0       |                |
+---------------------+---------+------+-----+---------+----------------+

Your select statement makes no sense. In the absence of an explicit order by, the value of price will not be deterministic and cease to retain any association with the other attributes. But this is moot since you have no primary or unique key being populated from the select . We can't fix thiß because we don't know what the data means/where it comes from and you've not given any example input & output. — symcbean, Jul 05 '17 at 22:22

score 0 · Answer 1 · answered Jul 06 '17 at 17:08

It looks like you have a key problem.

The "ON DUPLICATE KEY UPDATE" pays attention to the table's primary key only, in this case a combo primary of product_supplier_id plus product_id. The product_supplier_id field isn't being included in your INSERT, and is then being auto-generated.

If you really want to make this commit as a single action (instead of check for existing then choose to either insert or update) then you'll need to move the primary key to be based on a combo of product_id and supplier_id and drop the auto-increment field.

If you need to be able to have more than one price per product/supplier then you can't use ON DUPLICATE KEY UPDATE and will need to run multiple queries.

Insert with on duplicate key update ignores index

1 Answers1