CODEWITHSUNDEEP
sql
Alex
Alex

Reputation: 1999

Are There Any RDBMS That Can Update or Insert In One Statement?

If I have a table like this:

ID     FirstName     LastName     Address
--     ---------     --------     -------

I want to update info when there is an existing ID or insert a new row when the ID does not already exist.

Can this be done in one statement or do I have to check for the existance of the ID and then either insert or update?

Upvotes: 0

Views: 132

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Yes. There are three methods that come to mind:

merge is the most common method. SQL Server, Oracle, DB2, and Teradata support this. Refer to the appropriate documentation. Note that the exact merge syntax may vary from database to database.

on duplicate key update is a method used by (older versions of) MySQL and MariaDB. Refer to the appropriate documentation.

Postgres and SQLite supports conflict resolution via the on conflict clause in insert statements. That said, many databases are based older versions of Postgres, so not all derived databases support on conflict.

(I apologize for databases not listed. This is off the top of my head.)

If a database supports this functionality, then one of these three methods is probably used. You can always use transactions to get similar effects using multiple statements.

Upvotes: 1

Related Questions