数据库中有两个表,表1如下:
CREATE TABLE Table1
(
ID1 INTEGER,
AAA NUMBER,
BBB NUMBER,
CCC NUMBER,
DDD NUMBER,
);其中ID1和AAA为联合主键
表2:
CREATE TABLE Table2
(
ID2 INTEGER,
AAA NUMBER,
BBB2 NUMBER,
);其中ID2和AAA为联合主键
想把表2中的BBB2字段的数据(许多行)分别放入表1中的BBB、CCC字段中,当表1和表2中的联合主键相同时用下面语句:
insert into table (ID1, AAA, BBB) select ID2, AAA, BBB2 from table2 on duplicate key update BBB = values(BBB2);
insert into table (ID1, AAA, CCC) select ID2, AAA, BBB2 from table2 on duplicate key update CCC = values(BBB2);
怎么老是提示命令未正确结束?请高手帮忙解答下,谢了
CREATE TABLE Table1
(
ID1 INTEGER,
AAA NUMBER,
BBB NUMBER,
CCC NUMBER,
DDD NUMBER,
);其中ID1和AAA为联合主键
表2:
CREATE TABLE Table2
(
ID2 INTEGER,
AAA NUMBER,
BBB2 NUMBER,
);其中ID2和AAA为联合主键
想把表2中的BBB2字段的数据(许多行)分别放入表1中的BBB、CCC字段中,当表1和表2中的联合主键相同时用下面语句:
insert into table (ID1, AAA, BBB) select ID2, AAA, BBB2 from table2 on duplicate key update BBB = values(BBB2);
insert into table (ID1, AAA, CCC) select ID2, AAA, BBB2 from table2 on duplicate key update CCC = values(BBB2);
怎么老是提示命令未正确结束?请高手帮忙解答下,谢了
merge into table1
using table2
on(table1.id1=table2.id2 and table1.aaa=table2.aaa)
when matched then
update
set table1.bbb=table2.bbb2 ,table1.ccc=table2.ccc;
when not matched then
insert
values(table2.id2,table2.bbb2);
update table1 set bbb=(select bbb2 from table2
where table1.id=table2.id and table1.aaa=table2.aaa)
where exists(select null from table2
where table1.id=table2.id and table1.aaa=table2.aaa)
小弟刚入门,还请多指教!
update table1 a set (a.bbb,a.ccc)=(select b.bbb2,b.bbb2 from table2 b where b.id2=a.id1 and b.aaa=a.aaa)
where exists
(select 1 from table2 c
where a.id1=c.id2 and a.aaa=c.aaa)
where table1.id=table2.id and table1.aaa=table2.aaa)
--这个是更新数据用的where exists(select null from table2
where table1.id=table2.id and table1.aaa=table2.aaa)
--如果没有这个 那
些table1表中不符合where table1.id=table2.id and table1.aaa=table2.aaa这个条件的语句将会被更新为null
你可以测试下