How to Perform an UPSERT so that I can use both new and old values in update part

by

As mentioned in my comment, you don’t have to do the subselect to reference to the row that’s causing ON DUPLICATE KEY to fire. So, in your example you can use the following:

INSERT INTO `item`
(`item_name`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`new_items_count` = `new_items_count` + 27

Remember that most things are really simple, if you catch yourself overcomplicating something that should be simple then you are most likely doing it the wrong way 🙂

More Related Contents:

  1. Insert into a MySQL table or update if exists
  2. MyISAM versus InnoDB [closed]
  3. How can i optimize MySQL’s ORDER BY RAND() function?
  4. MYSQL OR vs IN performance
  5. Why does MYSQL higher LIMIT offset slow the query down?
  6. JDBC batch insert performance
  7. SQL Performance UNION vs OR
  8. Why is MySQL InnoDB insert so slow?
  9. MySQL vs MongoDB 1000 reads
  10. MySQL stored procedures use them or not to use them
  11. MySQL “IN” operator performance on (large?) number of values
  12. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  13. What is better in MYSQL count(*) or count(1)?
  14. MySql views performance [closed]
  15. MySQL view performance
  16. “SELECT COUNT(*)” is slow, even with where clause
  17. MySQL: Inner join vs Where [duplicate]
  18. Higher cardinality column first in an index when involving a range?
  19. MySQL load data infile – acceleration?
  20. MySQL: Many tables or many databases?
  21. sql group by versus distinct
  22. is php sort better than mysql “order by”?
  23. How can “set timestamp” be a slow query?
  24. Would it help to add index to BIGINT column in MySQL?
  25. ALTER TABLE ADD COLUMN takes a long time
  26. MySQL behavior of ON DUPLICATE KEY UPDATE for multiple UNIQUE fields
  27. Mysql performance on 6 million row table
  28. Disadvantages of quoting integers in a Mysql query?
  29. Composite Primary Key performance drawback in MySQL
  30. Optimizing MySQL for ALTER TABLE of InnoDB

Leave a Comment