CODEWITHSUNDEEP
mysqlinsertreplaceupsert
ToddN
ToddN

Reputation: 2961

INSERT when NOT duplicate row?

I simply want to replace (update) DATE, PRICE and OLDPRICE when a price is different than the PRICE in my table where MODEL is unique.

Sample Row Table Data:

DATE        |   MAKE   |   MODEL   |   PRICE   |   OLDPRICE
2012-04-15  | Advance  | DTA-100-X |   400     |   390

There must be a dozen ways to do this but I'm looking for the best solution to use in a MySQL query.

Should I be using:

  1. INSERT INTO..ON DUPLICATE KEY UPDATE
  2. REPLACE INTO..
  3. UPDATE...WHERE PRICE != XXX

Essential syntax would be to UPDATE if the MODEL is the same but the PRICE is different; OLDPRICE becomes PRICE on UPDATE

*UPDATE*

This REPLACES whether price changed or not. I only want updates/replaces if price changed ie, this should NOT update anything given above example, however it does because date is different:

REPLACE INTO MyTable (DATE, MAKE, MODEL, PRICE, OLDPRICE) VALUES ('2012-10-02', 'Advance', 'DTA-100-X', '400', '390')

Upvotes: 3

Views: 587

Answers (3)

Ike Walker
Ike Walker

Reputation: 65537

Based on your update, you should use option 1, the upsert, aka INSERT...ON DULICATE KEY UPDATE.

I think you are saying you only want to update the PRICE and OLDPRICE (not DATE or MAKE) if the model already exists with a difference price. If that's the case, this should work for you:

Like this:

INSERT INTO MyTable (DATE, MAKE, MODEL, PRICE, OLDPRICE) 
VALUES ('2012-10-02', 'Advance', 'DTA-100-X', '410', '390')
ON DUPLICATE KEY UPDATE OLDPRICE = CASE WHEN PRICE != VALUES(PRICE) THEN PRICE ELSE OLDPRICE END,
  DATE = CASE WHEN PRICE != VALUES(PRICE) THEN VALUES(DATE) ELSE DATE END,
  PRICE = VALUES(PRICE);

Upvotes: 0

Simon Forsberg
Simon Forsberg

Reputation: 13331

If you care about the order of the table, use INSERT INTO..ON DUPLICATE KEY UPDATE. REPLACE will delete the old row and then add a new one, if it finds a duplicate.

INSERT INTO..ON DUPLICATE KEY UPDATE does, as the name suggests, inserts a new row into the table unless there's a duplicate, in which case it will update the row (instead of removing it and adding a new one) which will make the order of the table the same.

Upvotes: 1

Asaph
Asaph

Reputation: 162791

MySQL has a REPLACE statement specifically for this purpose.

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

-- http://dev.mysql.com/doc/refman/5.6/en/replace.html

Upvotes: 2

Related Questions