How Oracle MERGE into is used

Very often we will appear, for example, if a piece of data already exists in the table, update it, assume that it does not exist, and insert the new data. Assuming that you don't use the merge syntax provided by Oracle, you might want to check the database select to see if it exists. Then decide how to operate, this kind of words need to write a lot of other code, the same time performance is not good, to go back and forth the database two times. You can complete a SQL statement with merge.

1) Main functions
Provides conditionally updated and inserted data into a database table
Assuming that the row exists, run an update operation, assuming it is a new row, run the insert operation
-Avoid a separate update
-Improve performance and ease of use
-Very useful in data warehouse applications


2) The syntax for the merge statement 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 isn't matched then merge_insert_clause;

3) Demo sample

MERGE into TABLE targetusing (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 ' T ARGET_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) tempon (TARGET. Store_no = TEMP. Store_no and TARGET. Target_ym = TEMP. TARGET_YM) when matched and 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 = Sysdatewhen 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:

It is not annoying to see so many fields in a database table, and it is difficult to copy, write, or make mistakes, such as the following:

1, first query out all the fields of the table

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, using the function concatenate

Extrapolate, very often can be used, organized from the network