MYSQL INSERT or UPDATE IF

Question

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)

Answer 1

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.

Answer 2

You could use the replace syntax in mysql.

But that would work only with a unique index on... the fields needing to be unique.

So you'd have to create a unique index like that :

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

then your insert/update syntax would become

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

EDIT

Example of 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 those above the solution I found in the end 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