mysql update 加if,MYSQL INSERT或UPDATE IF

Been looking around the web for a while now and don't seem to be able to come across anything similar to what I want. I know it's something to do with the way I'm writing my query but any help would be appreciated.

The basics of what I am trying to do is:

Insert some items into a table if it does not exist

Update an item if it does exist

It exists in the format:

name, barcode, item, quantity, location, price and date

name - can be used in several rows

barcode - is for a specific item but can be used as several locations

item - is the same as barcode but contains the name

quantity - self explanatory

location - this can be different locations

price - that is attached to a specific item

date - last time that item was purchased

The tricky thing is, a "name" can have several items (barcode and item) at different locations for different prices. The idea is that a customer can see how much they bought an item for at a set time, so they know how much they would need to sell it for.

However the price that they bought it at can vary so they need to create another row in the table if the price is different from a previous purchase.

The idea behind the whole thing is for it to record how much a "name" has of each item at each location and then the price they bought it at and when they last purchased it.

Hope that makes sense.

In psuedo code:

Insert into table if does not exist

- name, barcode, item, quantity, location, price and date

If name, barcode, item, location and price are the same

- Update quantity and date (if more recent)

解决方案

First, add a UNIQUE constraint on name, barcode, item, location and price.

ALTER TABLE tableX

ADD CONSTRAINT tableX_UQ

UNIQUE (name, barcode, item, location, price) ;

Then you can use INSERT INTO ... ON DUPLICATE KEY UPDATE:

INSERT INTO tableX

(name, barcode, item, location, price, quantity, date)

VALUES

(?, ?, ?, ?, ?, ?, ?)

ON DUPLICATE KEY UPDATE

quantity = CASE WHEN VALUES(date) > date

THEN quantity + VALUES(quantity) -- add quantity

ELSE quantity -- or leave as it is

END

, date = CASE WHEN VALUES(date) > date

THEN VALUES(date) ; -- set date to new date

ELSE date -- or leave as it is

END

REPLACE could also be used but there are differences in the behaviour (which especially matter if you have foreign keys). For details, see this question “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE” and the answer by @Bill Kawin which discusses the differences between INSERT IGNORE, INSERT ... ON DUPLICATE KEY and REPLACE.