SQL语句批量增改数据的SQL语句写法（DB2、MySql）

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
...