SQL: Insert only new rows/records into a table?

Question

I'm parsing a json feed routinely and need to insert only the newest users from the feed and ignore existing users.

I think what I need is ON DUPLICATE KEY UPDATE or INSERT IGNORE based on some searching but I'm not quite sure which is why I'm asking - so for example:

users
1     John
2     Bob

Partial JSON:

{ userid:1, name:'John' },
{ userid:2, name:'Bob' },
{ userid:3, name:'Jeff' }

From this feed I only want to insert Jeff. I could do a simple loop through all users and do a simple SELECT query and see if the user id is already in the table, if not I do an INSERT, however I suspect it won't be an efficient and practical method.

By the way, I'm using Zend_Db for the database interaction if anyone would like to cater a specific answer :) I don't mind a generic strategic solution though.

Answer 1

The ON DUPLICATE KEY UPDATE alternative allows you to refer the update vs. insert decision to the database:

INSERT INTO table (userid, name) VALUES (2, 'Bobby');
  ON DUPLICATE KEY UPDATE name = 'Bobby';

would update the name field to 'Bobby', if an entry with userid 2 already exists.

You can use it as an alternative to the INSERT IGNORE if you supply a noneffective operation to the UPDATE:

INSERT INTO table (userid, name) VALUES (2, 'Bobby');
  ON DUPLICATE KEY UPDATE name = name;

This would do nothing if userid 2 already exists, thus avoiding the warning and swallowing of other errors you'd get when using INSERT IGNORE.

---

Another alternative would be REPLACE:

REPLACE INTO table (userid, name) VALUES (2, 'Bobby');

This would do a normal insert if the userid 2 does not exist yet. If it does exist, it will delete the old entry first and then insert a new one.

---

Be aware that both versions are MySQL specific extensions to SQL.

Answer 2

For Zend Framework, what I do is a try/catch of the insert statement and take further action based on the exception code:

class Application_Model_DbTable_MyModel extends Zend_Db_Table_Abstract

    public function insert($aData, $bIgnore = false) 
    {

        try 
        {
            $id =  parent::insert($aData);
        } 
        catch(Zend_Db_Statement_Mysqli_Exception $e) 
        {
            // code 1062: Mysqli statement execute error : Duplicate entry
            if($bIgnore && $e->getCode() == 1062) 
            {
                // continue;
            } 
            else 
            {
                throw $e;
            }
        }
        return !empty($id) ? $id : false;
    }
}