MYSQL INSERT or UPDATE IF

Question

I looked around the net for a while and seemed unable to find anything similar to what I wanted. I know this is due to the way I write my request, but any help would be appreciated.

The basics of what I'm trying to do is:

• Insert some items into the table if it does not exist
• Update item if it exists

It exists in the format:

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

name - can be used in several lines a barcode - for a specific element, but can be used as several locations item - this is the same as a barcode, but contains a name quantity - an obvious location - it can be different locations price - this binding to a specific item date - the last time this item was purchased

The difficulty is that a "name" can have several elements (barcode and element) in different places at different prices. The idea is that the customer can see how much they bought the item at a specific time, so they know how much they will need to sell.

However, the price they bought may vary, so they need to create another row in the table if the price is different from the previous purchase.

The idea of ​​all this is to write down how much the “name” has each element in each place, and then the price they bought and when they last bought it.

Hope this makes sense.

In the 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) 

Answer 1

First add a UNIQUE to 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 can also be used, but there are differences in behavior (which is especially important if you have foreign keys). See this INSERT IGNORE vs INSERT ... ON DUPLICATE KEY UPDATE question and @Bill Kawin answer, which discusses the differences between INSERT IGNORE , INSERT ... ON DUPLICATE KEY and REPLACE INSERT ... ON DUPLICATE KEY .

Answer 2

You can use replace syntax in mysql.

But this will only work with a unique index on ... fields that must be unique.

So you need to create a unique index:

 alter <yourtable> add unique index(name, barcode, item, location, price); 

then your insert / update syntax will become

 replace into <yourtable> (name, barcode, item, quantity, location, price, date) VALUES('name1', 'barcode1', 2, 'location1', 12.0, '25/12/2012'); 

EDIT

An example of a stored procedure (simplified and untested):

 DELIMITER && DROP PROCEDURE IF EXISTS MyBase.UpdateOrInsert $$ CREATE PROCEDURE MyBase.UpdateOrInsert ( IN _name VARCHAR(10), IN _barcode VARCHAR(50), IN _quantity INTEGER IN _date DATE ) DECLARE existingDate DATE default NULL; BEGIN SELECT date INTO existingDate FROM <yourTable> where name = _name and barcode = _barcode; if (existingDate IS NULL) then insert into <yourtable> (name, barcode, quantity, date) VALUES(_name, _barcode, _qantity, _date); else if (existingDate < _date) then update <yourtable> set quantity = _quantity, date = _date where name = _name and barcode = _barcode; end if; end if; END && 

Answer 3

Thanks to all the help from the above solutions, which I found at the end, it was very close to both. Find it below:

 INSERT INTO `stock` (name, barcode, item, quantity, location, price, date) VALUES (?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE quantity = CASE WHEN VALUES(date) < $date THEN quantity + $quantity ELSE quantity END, date = CASE WHEN VALUES(date) < $date THEN VALUES(date) ELSE $date END