Introduction to the usage examples of Oracle MERGE into

This article mainly introduces the use of Oracle MERGE into, the need for friends can refer to the following

A lot of times we have the situation where a piece of data already exists in the table, update it, and if it doesn't, insert the new data. If you don't use the merge syntax provided by Oracle, you might want to go to a database select query to see if it exists, and then decide what to do. That way, you need to write more code, and the performance is not good, to the database two times. Use the merge Word to complete a SQL statement.

1) Main function

Provides conditional updating and inserting of data into a database table

If the row exists, an update operation is performed, and if it is a new row, an insert operation is performed

-Avoid a separate update

-Improve performance and ease of use

-Useful in data warehousing applications

2 The syntax of the merge statement is as follows:

The code is as follows:

MERGE [hint] into [schema.] table [T_alias] USING [schema.]

{Table | view | subquery} [T_alias] On (condition)

When matched THEN Merge_update_clause

When not matched THEN merge_insert_clause;

3) Example

The code is as follows:

MERGE into TABLE TARGET

USING (SELECT ' 111111111 ' store_no,

' 2014-01 ' Target_ym,

' 1 ' target_d01,

' 2 ' target_d02,

' 2 ' target_d03,

' 2 ' target_d04,

' 2 ' target_d05,

' 2 ' target_d06,

' 2 ' target_d07,

' 2 ' target_d08,

' 2 ' target_d09,

' 2 ' target_d10,

' 2 ' target_d11,

' 2 ' target_d12,

' 2 ' target_d13,

' 2 ' Target_d14,

' 2 ' target_d15,

' 2 ' target_d16,

' 2 ' target_d17,

' 2 ' target_d18,

' 2 ' target_d19,

' 2 ' target_d20,

' 2 ' target_d21,

' 2 ' target_d22,

' 2 ' target_d23,

' 2 ' target_d24,

' 2 ' target_d25,

' 2 ' target_d26,

' 2 ' target_d27,

' 2 ' target_d28,

' 2 ' target_d29,

' 2 ' target_d30,

' 2 ' target_d31,

1 user_id

From DUAL) TEMP

On (TARGET. Store_no = TEMP. Store_no and TARGET. Target_ym = TEMP. TARGET_YM)

When matched THEN

UPDATE

SET TARGET. TARGET_D01 = TEMP. TARGET_D01,

TARGET. TARGET_D02 = TEMP. TARGET_D02,

TARGET. TARGET_D03 = TEMP. TARGET_D03,

TARGET. TARGET_D04 = TEMP. TARGET_D04,

TARGET. TARGET_D05 = TEMP. TARGET_D05,

TARGET. Target_d06 = TEMP. TARGET_D06,

TARGET. Target_d07 = TEMP. TARGET_D07,

TARGET. Target_d08 = TEMP. TARGET_D08,

TARGET. target_d09 = TEMP. TARGET_D09,

TARGET. TARGET_D10 = TEMP. TARGET_D10,

TARGET. Target_d11 = TEMP. Target_d11,

TARGET. Target_d12 = TEMP. TARGET_D12,

TARGET. TARGET_D13 = TEMP. TARGET_D13,

TARGET. TARGET_D14 = TEMP. TARGET_D14,

TARGET. Target_d15 = TEMP. TARGET_D15,

TARGET. Target_d16 = TEMP. Target_d16,

TARGET. Target_d17 = TEMP. Target_d17,

TARGET. Target_d18 = TEMP. Target_d18,

TARGET. TARGET_D19 = TEMP. TARGET_D19,

TARGET. TARGET_D20 = TEMP. TARGET_D20,

TARGET. Target_d21 = TEMP. Target_d21,

TARGET. Target_d22 = TEMP. Target_d22,

TARGET. Target_d23 = TEMP. TARGET_D23,

TARGET. TARGET_D24 = TEMP. TARGET_D24,

TARGET. TARGET_D25 = TEMP. TARGET_D25,

TARGET. Target_d26 = TEMP. TARGET_D26,

TARGET. Target_d27 = TEMP. TARGET_D27,

TARGET. TARGET_D28 = TEMP. TARGET_D28,

TARGET. target_d29 = TEMP. Target_d29,

TARGET. Target_d30 = TEMP. Target_d30,

TARGET. Target_d31 = TEMP. Target_d31,

TARGET. Opt_counter = TARGET. Opt_counter + 1,

TARGET. update_by = TEMP. USER_ID,

TARGET. Update_date = Sysdate

When not matched THEN

INSERT

VALUES

(SEQ. Nextval,

TEMP. Store_no,

TEMP. Target_ym,

TEMP. TARGET_D01,

TEMP. TARGET_D02,

TEMP. TARGET_D03,

TEMP. TARGET_D04,

TEMP. TARGET_D05,

TEMP. TARGET_D06,

TEMP. TARGET_D07,

TEMP. TARGET_D08,

TEMP. TARGET_D09,

TEMP. TARGET_D10,

TEMP. Target_d11,

TEMP. TARGET_D12,

TEMP. TARGET_D13,

TEMP. TARGET_D14,

TEMP. TARGET_D15,

TEMP. Target_d16,

TEMP. Target_d17,

TEMP. Target_d18,

TEMP. TARGET_D19,

TEMP. TARGET_D20,

TEMP. Target_d21,

TEMP. Target_d22,

TEMP. TARGET_D23,

TEMP. TARGET_D24,

TEMP. TARGET_D25,

TEMP. TARGET_D26,

TEMP. TARGET_D27,

TEMP. TARGET_D28,

TEMP. Target_d29,

TEMP. Target_d30,

TEMP. Target_d31,

Null

DEFAULT,

DEFAULT,

Null

TEMP. USER_ID,

DEFAULT,

Null

NULL);

Small help:

See the database table so many fields is not annoying, copy, write all trouble is also easy to error, you can do the following:

1, first query out all the fields of the table

Copy code code as follows:

SELECT column_id,

COLUMN_NAME,

Data_type,

Data_length,

Data_precision,

Data_scale,

NULLABLE,

Data_default

From User_tab_columns

WHERE table_name = ' TABLE '

ORDER BY column_id

2, copy the table column name to Excel, use the function concatenate

Extrapolate, most of the time can be used, organized from the network

Note : Please pay attention to the triple Programming Tutorials section for more wonderful articles .