通过select插入表前如何检查重复项: 0 d. H9 b9 U( Z4 W% C6 ninsert into table1select col1,col2 from table2我需要检查table是否已有table1.col1.value = table2.col1.value如果是,从插入中排除行。* _( W8 K& G% ~- g5 k5 R6 |
" }4 z @& g3 k% D2 @1 y3 j" Z解决方案: " v6 w. |$ A6 j, O INSERT INTO table1 SELECT t2.col t2.col2 FROM table2 t2 LEFT JOIN table1 t1 ON t2.col1 = t1.col1 AND t2.col2 = t1.col2 WHERE t1.col1 IS NULL替代使用9 H; G/ O( r; [0 }; ]$ R0 i
INSERT INTO @table2 SELECT col col2 FROM table1 EXCEPT SELECT t1.col t1.col2 FROM table1 t1 INNER JOIN table2 t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2使用不存在的替代方法 : @2 p" _# r% I; U4 kINSERT INTO table2 SELECT col1,col2 FROM table1 t1WHERENOT EXISTS( SELECT 1 FROM table2 t2 WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2)