Update/Insert (Upsert) Multiple Records in Different DB Types
When you write a software application that connects to a database, it is better to support it for different types of databases. WSO2 Identity Server is a good example. It supports different DB types.
If you download the WSO2 Identity Server (IS) and navigate to <IS-HOME>/dbscripts you can find DDL (Data Definition Language) (i.e create and modify database objects such as tables, indexes) for different database types. Thus, when you develop a feature that needs to connect with the DB, you should write queries that support every database type. If you come across such a situation and want to use an upsert query, I hope this blog is useful for you.
What is UPSERT ??
You may wonder what this “Upsert” means ?? First thing First!
Upsert is a word derived based on update or insert. This is a database operation that is used to update/insert rows in a table based on the particular condition satisfaction.
Let's use the following database table called “PERSON” which has three columns as ID, NAME, and DESCRIPTION. Further, “ID” is the Primary key of this table.
In this blog post, I’m talking about how to upsert multiple rows to a particular table without duplicating its primary key. In other words, if the data given to the query contains an ID that already exists the corresponding record will be updated. Otherwise, a new record will be inserted. As an example, I use two rows for the upsert, but please note you can use these queries for any number of records.
Let’s begin !!
MYSQL
In MYSQL we have a special keyword for this task. It is INSERT … ON DUPLICATE KEY UPDATE statement.
Here, I have given the prepared statement. You can try it by replacing values for ‘?’ marks.
POSTGRESQL
Similar to INSERT … ON DUPLICATE KEY UPDATE in MYSQL, ON CONFLICT(<KEY>) DO UPDATE SET is the keyword for this task.
MSSQL
MERGE statement in MSSQL is suitable for our task. Here is the general definition of the MERGE query.
According to that, our target and source tables should be the same. What we do is, take the records we are trying to insert/update as the source table. Then check on the condition that the target table’s ID = source table’s ID.
NOTE: You may get the following error message even though you have terminated the query with a semicolon. It is an issue with some SQL editors. I got this error when using DBeaver. However, the above query successfully executed on the terminal.
DB2
The same query used in MSSQL can be used in DB2.
ORACLE
MERGE statement can be used in oracle as same as the concept described under MSSQL. However, it has a tinny difference when defining the source table.
NOTE: You can find more details about the Oracle Dual table from here.
Hope you could learn a lot in one place. Don’t forget to applause if you get something. Cheers !!
📝 Save this story in Journal.
👩💻 Wake up every Sunday morning to the week’s most noteworthy stories in Tech waiting in your inbox. Read the Noteworthy in Tech newsletter.
