Monday, May 3, 2010

Upserts in various database systems

Upsert is a common database term that is used to signify the operation "Update the row... if an entity with the same key does not exist then insert it"

Here's how I do it in various databases:

MYSQL

REPLACE / IGNORE Keywords
It follows the same syntax as insert except that it replaces the old row if the same entity exists:
REPLACE INTO tablename VALUES ( 'whatever' )
In case you want to ignore the insert in case the keys exists you could do:
INSERT IGNORE INTO tablename VALUES ( 'whatever' )

SQLite
Did I tell you I love SQLite? Anyways:
ON CONFLICT clause

Microsoft SQL Server
Didn't encounter the REPLACE but used ignore quite commonly. You can acutally do it on a table level

CREATE TABLE TEST(
    [ID] [int] NOT NULL,
     CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
    (
        [ID] ASC   
    )WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]

) ON [PRIMARY]







Enjoy!