如下SQL,大表500列,请问有没有什么办法能够一次插入或者更新整行数据。
T1和T2结构完全一样。c1是主键。
MERGE INTO T T1
USING (SELECT * FROM T3, T4, T5 ... where ...) T2
ON ( T1.c1=T2.c1)
WHEN MATCHED THEN
UPDATE SET T1.c1 = T2.c1,
T1,c2 = T2.c2,
... //此处省略500行
T1.c500 = T2.c500
WHEN NOT MATCHED THEN
INSERT (c1, c2, ... c500) VALUES(T2.c1,T2.c2 ... T2.c500);
换个思路,就是把t1能匹配到t2的都删掉然后吧t2全表插入,这样的话直接delete 和 insert select* ,就不用写那么多列了
执行两个 SQL:
1. delete from t1 where exists (select * from t2 where t1.c1 = t2.c1) 把第一个表中和第二个表匹配的都删掉
2. insert into t1 select * from t2 把第二表直接插入第一个