insert into test_CC2(ID , NAME ,FLAG) (select id,name,to_char(sum(to_number(flag))) as flag from test_cc group by id,name ); 结果 报错 不是 group by 表达式
--update merge into test_cc a using ( select id,name,to_char(sum(to_number(flag))) as flag from test_cc group by id,name ) b on (a.id=b.id and a.name=b.name) when matched then update set a.flag = b.flag;--delete delete from test_cc a where a.rowid > (select min(b.rowid) from test_cc b where a.id=b.id and a.name=b.name and a.flag=b.flag)
缺少关键字 when matched 后面,merge 不太熟悉
无语 insert into test_cc2(id,name,flag)( select id,name,to_char(sum(to_number(flag))) as flag from test_cc group by id,name ); 原来这样就可以。
select id,name,to_char(sum(to_number(flag))) as flag
from test_cc
group by id,name
不行哦 缺少 INTO 语句,而且 我是要更新这个表
from TEST_CC a
group by a.id, a.name
(select id,name,to_char(sum(to_number(flag))) as flag
from test_cc
group by id,name
);
结果 报错 不是 group by 表达式
--update
merge into test_cc a
using
(
select id,name,to_char(sum(to_number(flag))) as flag
from test_cc
group by id,name
) b
on (a.id=b.id and a.name=b.name)
when matched then
update set a.flag = b.flag;--delete
delete from test_cc a where a.rowid > (select min(b.rowid) from test_cc b where a.id=b.id and a.name=b.name and a.flag=b.flag)
insert into test_cc2(id,name,flag)(
select id,name,to_char(sum(to_number(flag))) as flag
from test_cc
group by id,name
);
原来这样就可以。