Sunday, July 12, 2009

MySQL - ON DUPLICATE KEY

I was adding bridal registry functionality to CartWeaver 3 and was wondering if MySQL, the site is running on ColdFusion with MySQL, has something similar to T-SQL's MERGE. Not only does it have, but the code looks a lot more elegant. One thing to remember though, is that you must set cart/registry user id + sku/product id as UNIQUE.
Here's how this works. You do your regular insert and at the end add a ON DUPLICATE KEY:
INSERT INTO tbl_registryproducts(reguserid, skuid, qty)
VALUES(#session.reguserid#,#form.skuid#,#form.qty#)
       ON DUPLICATE KEY UPDATE qty = qty + #form.qty#
That's it. One line and you're done. And yes, I know I should be using cfqueryparam, but that would make this example a lot longer and a lot less readable.

2 comments:

  1. Cool! What about updating multiple columns from a single sub-select from another table?

    ReplyDelete
  2. Not sure what you mean.
    INSERT INTO
    SELECT
    FROM
    ?

    ReplyDelete