SQL Server merge function usage _ MySQL

The WHENMATCHEDTHEN statement of the ON matching condition in the USING source table of the MERGE target table cannot be omitted by the semicolon, the source table can be either a table or a subquery statement. WHENNOTMATCHEDBYTARGET indicates the target... MERGE target table

USING Source table

ON matching condition

WHEN MATCHED THEN

Statement

WHEN NOT MATCHED THEN

Statement;

The last statement semicolon cannot be omitted, and the source table can be either a table or a subquery statement.

WHEN NOT MATCHED BY TARGET

It indicates that the TARGET table does NOT match. by target is the default value. Therefore, we use the WHEN NOT MATCHED THEN

WHEN NOT MATCHED BY SOURCE

Indicates that the source table does not match, that is, the target table exists and the source table does not exist.

Main usage:

Merge cannot update or delete the same row multiple times.

When the source and target tables do not match:

If the source table does not have a target table, insert the table.

If the data is not in the source table but in the target table, update or delete the data.

When the source Table matches the target table:

Update or delete operations

The when matched clause can have two clauses. when there are two clauses, the first clause must be when matched and condition, and the two matched clauses only execute one clause, and the two clauses must

Is an update and a delete operation.

When not matched by source is similar to the above

merge icr_codemap_bak as ausing icr_codemap as bon a.COLNAME = b.COLNAME and a.ctcode = b.ctcodewhen matched and b.pbcode <> a.pbcodethen update set a.pbcode = b.pbcodewhen not matchedthen insert values(b.colname,b.ctcode,b.pbcode,b.note);

You can compare different fields for updates.

Https://technet.microsoft.com/zh-cn/library/bb510625.aspx. this is the site of MSDN.

In the Merge Matched operation, only UPDATE or DELETE statements can be executed.

In the Merge Not Matched operation, only INSERT statements can be executed.

A Matched operation in a Merge statement can only contain one UPDATE or DELETE statement, otherwise, the following error will occur-An action of type 'When matched' cannot appear more than once in a 'update' clause of a MERGE statement.

The Merge statement must end with a semicolon.

The above is the usage of the merge function of SQLServer _ MySQL. For more information, see The PHP Chinese website (www.php1.cn )!