DB2
merge语句
MERGE INTO SCHEMA1.TABLE1 AS A
USING SCHEMA2.TABLE2 AS B
ON A.XXX = B.YYY
WHEN MATCHED THEN UPDATE SET
A.AAA = B.AAA,
A.BBB = B.BBB,
A.CCC = B.CCC
...
WHEN NOT MATCHED THEN INSERT VALUES
(B.AAA,B.BBB,B.CCC...省略)
注意: 判断条件是ON后面的条件
MySql
ON DUPLICATE语句
INSERT INTO SCHEMA1.TABLE1
(AAA,BBB,CCC...省略)
SELECT
AAA,BBB,CCC...省略
FROM SCHEMA2.TABLE2
ON DUPLICATE KEY UPDATE
SCHEMA1.TABLE1.AAA = SCHEMA2.TABLE2.AAA,
SCHEMA1.TABLE1.BBB = SCHEMA2.TABLE2.BBB,
SCHEMA1.TABLE1.CCC = SCHEMA2.TABLE2.CCC
...省略
注意: 对于MySql数据库必须有PRIMARY KEY或UNIQUE索引,作为SQL执行的区分判断,否则此SQL等同于INSERT INTO …
mybatis环境下
mysql批量update 1
INSERT INTO SCHEMA1.TABLE1(AAA,BBB,CCC...省略) VALUES
<foreach collection="list" item="item" separator=",">
(#{item.aaa},#{item.bbb},#{item.ccc})
</foreach>
ON DUPLICATE KEY UPDATE
AAA = VALUES(AAA),
BBB = VALUES(BBB),
CCC = VALUES(CCC)
mysql批量update 2
UPDATE SCHEMAL.TABLE1 AS T1
INNER JOIN (
SELECT DISTINCT * FROM (
<foreach collection="list" item="item" separator="union ">
SELECT
'${item.AAA}' AS AAA,
'${item.BBB}' AS BBB,
'${item.CCC}' AS CCC,
...
FROM SCHEMAL.TABLE1
</foreach>
) AS T0
) AS T2 ON T1.AAA = T2.AAA
SET
T1.BBB = T2.BBB,
T1.CCC = T2.CCC
...