重复密钥更新..数据库密钥？

Question

I have a table (id, name, price, menu_id)

I have a page in which every item's name and price is shown, and the users can edit it.

But when I submit, it creates new rows.

The index is on the primary key. Should it be on something else?

I tried the following:

INSERT INTO gerechten 
SET gerecht_naam = :naam, gerecht_prijs = :prijs, menu_id = :menuid 
ON DUPLICATE KEY 
UPDATE gerecht_naam = :naam, gerecht_prijs = :prijs

Answer 1

your query will transform to an update whenever any unique key crashes in your table. Up to you to decide where this key should be placed on. For example, your query could be:

INSERT INTO `gerechten` (`name`, `price`, `menu_id`) 
VALUES  (:name, :price, :menuid)
ON DUPLICATE KEY UPDATE 
    name = :name, 
    price = :price

with a unique index on name AND menu_id for example. (I believe there can be several times the same name as long as it is in a different menu)

Of course if there are various fields combinations that must be unique this is a problematic situation and maybe you should use a different approach, such as a select ... lock statement to check previous existence of the values being edited.