Скачать или смотреть How to Use Conditional Insert Statements in PL/SQL

Learn how to efficiently insert records into your PL/SQL database only when certain conditions are met, using the `MERGE` statement.
---
This video is based on the question https://stackoverflow.com/q/70094814/ asked by the user 'Java Programmer' ( https://stackoverflow.com/u/1407184/ ) and on the answer https://stackoverflow.com/a/70094853/ provided by the user 'Littlefoot' ( https://stackoverflow.com/u/9097906/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to add conditional insert statements in PLSQL

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

Are you working with a PL/SQL database and feeling stuck on how to insert records conditionally? Specifically, if you want to avoid duplicates in your database, it’s important to have a method for inserting rows only when certain criteria are met. In this guide, we will explore the problem of conditional insert statements in PL/SQL and walk through a solution using the MERGE statement.

The Problem

Suppose you have a database with two tables: customer and states. The customer table contains customer information, and the states table has a record of states for each customer. The goal is to insert a row in the states table for each customer with the state name 'Sleeping'. However, you only want to execute the insert if that state does not already exist in the states table.

For example, if there are three customers in the customer table, you might initially use a simple insert statement like this:

[[See Video to Reveal this Text or Code Snippet]]

This query will insert three new records into the states table associated with each customer. However, the issue arises if 'Sleeping' already exists in the states table for any given customer, which would lead to unwanted duplicates.

The Solution: Using MERGE

To handle this situation effectively, you can use the MERGE statement, which allows you to insert records conditionally. Here's how it works:

The MERGE Statement

The MERGE statement combines the functionality of the INSERT, UPDATE, and DELETE statements. It checks for existing records and updates them if they match a specified condition, or inserts new records when they do not match.

The PL/SQL Command

You can use the following MERGE command to insert the state 'Sleeping' for each customer only if that entry doesn't already exist in the states table:

[[See Video to Reveal this Text or Code Snippet]]

Breaking It Down

merge into states s: This specifies the target table (states) and gives it an alias (s).

using customer c: This specifies the source table (customer) and assigns it an alias (c).

on (...): This is where you define the condition to check before inserting a new record. The condition verifies if the customer ID and state name already exist in the states table.

when not matched then insert (...): This part of the statement specifies what action to take when the condition is not met (i.e., the record does not exist). It outlines which fields to insert and the values to use.

Conclusion

The use of the MERGE statement in PL/SQL allows for efficient, conditional inserts into your database, preventing the risk of duplicate records. This not only simplifies your SQL queries but also maintains data integrity within your tables. So the next time you want to insert records based on specific conditions, remember to utilize MERGE for optimal results.

By following the steps outlined above, you can confidently incorporate conditional insert statements in your PL/SQL environment.