MERGE INTO 사용법 데이터가 있으면 UPDATE 없으면 INSERT

MERGE INTO 사용법

Introduction

In SQL, the MERGE INTO statement allows you to perform both an INSERT and an UPDATE operation based on a condition. This can be particularly useful when you want to update existing records, or insert new ones if they don't already exist in the target table.

Syntax

The basic syntax for MERGE INTO statement is as follows:

```sql
MERGE INTO target_table
USING source_table ON condition
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (value1, value2, ...)

Explanation

target_table: This is the table that you want to update or insert data into.
source_table: This is the table that provides the data for the update or insert operation.
ON condition: This specifies the condition to match records between the target and source tables.
WHEN MATCHED THEN: This section defines what should happen when a match is found between the target and source tables. Here, you can specify the column(s) and value(s) to update.
WHEN NOT MATCHED THEN: This section defines what should happen when no match is found between the target and source tables. Here, you can specify the column(s) and value(s) to insert.

Example

Let's say we have a customers table with the following structure:

```sql
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
)

Now, using the MERGE INTO statement, we can update existing customer records based on the id column, or insert new records if they don't exist:

```sql
MERGE INTO customers AS target
USING (SELECT * FROM new_customers) AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.name = source.name, target.email = source.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email)
    VALUES (source.id, source.name, source.email)

In the above example, we are merging the new_customers table with the customers table based on the id column. If a match is found, the existing customer record is updated with the new values. If no match is found, a new record is inserted.

Conclusion

The MERGE INTO statement is a powerful tool in SQL for performing conditional updates and inserts. It allows you to streamline your database operations and ensure data consistency. Try using this statement in your own projects to efficiently handle updates and inserts in a single operation.

yynt

MERGE INTO 사용법 데이터가 있으면 UPDATE 없으면 INSERT