CODEWITHSUNDEEP
mysqlsqlsqlitemerge
midhunhk
midhunhk

Reputation: 5554

Does MySQL or SQLite support 'INSERT OR UPDATE' Statements?

The "INSERT OR UPDATE" statements are available in Oracle if i am not wrong. The possibility to create a new row if the primary key doesn't exists or update that row otherwise. Or is it only possible using some Stored Procedure or Stored Function?

Upvotes: 2

Views: 1343

Answers (1)

tofutim
tofutim

Reputation: 23374

MySQL:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

For SQLite, you would use ON CONFLICT REPLACE

onconflict - See http://www.sqlite.org/lang_conflict.html

REPLACE When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. If a NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces the NULL value with he default value for that column, or if the column has no default value, then the ABORT algorithm is used. If a CHECK constraint violation occurs, the REPLACE conflict resolution algorithm always works like ABORT.

When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.

The update hook is not invoked for rows that are deleted by the REPLACE conflict resolution strategy. Nor does REPLACE increment the change counter. The exceptional behaviors defined in this paragraph might change in a future release.

Upvotes: 6

Related Questions