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 ¶½²ì¶½°ì  
Ïà¹ØÎĵµ£º
SQL Server 2005 ÈÕ־ɾ³ýºÍÈÕÖ¾ÎļþÏÞÖÆ(ת)
2009Äê08ÔÂ04ÈÕ ÐÇÆÚ¶þ ÏÂÎç 04:40
Çå³ýÈÕÖ¾£º
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE szwzcheck ......
1. select replace(CA_SPELL,' ','') from hy_city_area È¥³ýÁÐÖеÄËùÓпոñ
2. LTRIM£¨£© º¯Êý°Ñ×Ö·û´®Í·²¿µÄ¿Õ¸ñÈ¥µô
3. RTRIM£¨£© º¯Êý°Ñ×Ö·û´®Î²²¿µÄ¿Õ¸ñÈ¥µô
4. select LOWER(replace(CA_SPELL,' ','')) f ......
ÎÊÌ⣺¼ÙÉèÓÐÕÅѧÉú³É¼¨±í(tb)ÈçÏÂ:
ÐÕÃû ¿Î³Ì ·ÖÊý
ÕÅÈý ÓïÎÄ 74
ÕÅÈý Êýѧ 83
ÕÅÈý ÎïÀí 93
ÀîËÄ ÓïÎÄ 74
ÀîËÄ Êýѧ 84
ÀîËÄ ÎïÀí 94
Ïë±ä³É(µÃµ½ÈçϽá¹û)£º
ÐÕÃû ÓïÎÄ Êýѧ ÎïÀí
---- ---- --- ......
1¡¢´¥·¢Æ÷µÄ¸ÅÄî
´¥·¢Æ÷Ò²ÊÇÒ»ÖÖ´øÃûµÄPL/SQL¿é¡£´¥·¢Æ÷ÀàËÆÓÚ¹ý³ÌºÍº¯Êý£¬ÒòΪËüÃǶ¼ÊÇÓµÓÐÉùÃ÷¡¢Ö´ÐкÍÒì³£´¦Àí¹ý³ÌµÄ´øÃûPL/SQL¿é¡£Óë°üÀàËÆ£¬´¥·¢Æ÷±ØÐë´æ´¢ÔÚÊý¾Ý¿âÖв¢ÇÒ²»Äܱ»¿é½øÐб¾µØ»¯ÉùÃ÷¡£
¶ÔÓÚ´¥·¢Æ÷¶øÑÔ£¬µ±´¥·¢Ê¼þ·¢ÉúµÄʱºò¾Í»áÏÔʽµØÖ´Ðиô¥·¢Æ÷£¬²¢ÇÒ´¥·¢Æ÷²»½ÓÊܲÎÊý¡£
´´½¨´¥·¢Æ÷µÄÓï·¨È ......
oracle ·½·¨ & ......