Example of OracleMERGEINTO usage

This article mainly introduces the usage of OracleMERGEINTO. For more information, see the following situations. If a piece of data already exists in the table, update it. If it does not exist, insert new data. if you do not use the merge syntax provided by Oracle, you may first query the database select to see if it exists, and then decide

This article mainly introduces the usage of Oracle merge into. For more information, see the following situations. If a piece of data already exists in a table, update it, if it does not exist, insert new data. if you do not use the merge syntax provided by Oracle, you may first query the database select to see if it exists, and then decide

This article mainly introduces the usage of Oracle merge into. For more information, see

In many cases, the following situation occurs: if a piece of data already exists in the table, update it. If it does not exist, insert new data. if you do not use the merge syntax provided by Oracle, you may first need to query the database select to see if it exists and then decide how to operate it. In this case, you need to write more code, at the same time, the performance is not good, it needs to go back and forth to the database twice. if merge is used, an SQL statement can be completed.

1) Main Functions

Provides conditional updates and inserts of data into database tables.

If this row exists, execute an UPDATE operation. If it is a new row, execute the INSERT operation.

-Avoid separate updates

-Improved performance and ease of use

-Useful in data warehouse 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 '000000' 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 );

Help:

Check whether there are so many fields in the database table, and the copy and write operations are both troublesome and error-prone. You can perform the following operations:

1. First query all fields in the table

The Code is 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 and use the CONCATENATE function.

It can be used in many cases and organized from the network.

Note: For more exciting articles, please pay attention to the section of the triple Programming Tutorial.