SQLÓï¾äPART7

Merge statement
function benefits: 1) provides the ability to conditionally update, insert or delete data into a database table. 2) performs an update if the row exists, and an insert if it is a new row. --> 1) avoids seperate updates, 2) increase performance and ease of use. 3) is useful in data warehousing applications.
syntax:
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col1_val,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
e.g.: table: temp_t1:
results:
1    1    ÊÕÎÄ            SHOUWEN
2    1    ·¢ÎÄ            FAWEN
3    1    ½»°ì            JIAOBAN
4    2    Öµ°à±¨¸æ     REPORT
5    3    ¶½²ì¶½°ì     SUPERVISAL
6    2    Öµ°à¿ì±¨     DAILYREPORT
7    2    ÎÒµÄÊÂÇé     NOTIFY
8    4    Ìá°¸            RESOLUTION
9    4    ½¨Òé            SUGGESTION
e.g.: table: temp_t2£º
results:
1    1    ÊÕÎÄ            SHOUWEN
2    1    ·¢ÎÄ            FAWEN
3    1    ½»°ì            JIAOBAN
4    2    Öµ°à±¨¸æ     REPORT
5    3    ¶½²ì¶½°ì