Geek asks and answers

INSERT if not duplicate the row?

I just want to replace (update) DATE, PRICEand OLDPRICEwhen the price is different from the PRICE in my table, where it MODELis unique.

Example row table data:

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

There should be dozens of ways to do this, but I'm looking for the best solution to use in a MySQL query.

Should I use:

  • INSERT INTO..ON DUPLICATE KEY UPDATE
  • REPLACE INTO..
  • UPDATE...WHERE PRICE != XXX

The essential syntax will be UPDATEif it is the MODELsame, but PRICEdifferent; OLDPRICEbecomes PRICEonUPDATE

* UPDATE *

This REPLACES whether the price has changed or not. I only want updates / replacements when the price changes, i.e. This should NOT update anything above, but this is because the date is different:

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

MySQL REPLACE statement .

REPLACE , INSERT, , , PRIMARY KEY UNIQUE, , ​​ .

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

+2

, INSERT INTO..ON DUPLICATE KEY UPDATE. REPLACE , , .

INSERT INTO..ON DUPLICATE KEY UPDATE , , , , ( ), .

+1

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

I think you say you want to update PRICE and OLDPRICE (not DATE or MAKE) if the model already exists with a difference price. In this 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);
0
source

More articles:


All Articles