ON DUPLICATE KEY UPDATE

MySQL has something like this:

INSERT INTO visits (ip, hits)
VALUES ('127.0.0.1', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;

As far as I'm know this feature doesn't exist in SQLite, what I want to know is if there is any way to archive the same effect without having to execute two queries. Also, if this is not possible, what do you prefer:

  • SELECT + (INSERT or UPDATE) or
  • UPDATE (+ INSERT if UPDATE fails)

  • INSERT OR IGNORE INTO visits VALUES ($ip, 0);
    UPDATE visits SET hits = hits + 1 WHERE ip LIKE $ip;
    

    This requires the "ip" column to have a UNIQUE (or PRIMARY KEY) constraint.


    EDIT: Another great solution: https://stackoverflow.com/a/4330694/89771.


    I'd prefer UPDATE (+ INSERT if UPDATE fails) . Less code = fewer bugs.


    The current answer will only work in sqlite OR mysql (depending on if you use OR or not). So, if you want cross dbms compatibility, the following will do...

    REPLACE INTO `visits` (ip, value) VALUES ($ip, 0);
    
    链接地址: http://www.djcxy.com/p/19788.html

    上一篇: 在PostgreSQL中插入重复更新?

    下一篇: 在DUPLICATE KEY UPDATE上