merge into pm_vendor_item_relation_def_t a using (select company_code, vendor_code,item_code,item_name,vendor_name from srm_vendor_credit) b
on (a.company_code = b.company_code
and a.vendor_code = b.vendor_code
and a.item_code = b.item_code)
when matched then
update set a.vendor_name = b.vendor_name,a.item_name=b.item_name;
when not matched then
insert--(a.company_code, a.vendor_code,a.item_code,a.item_name,a.vendor_name)
values(b.company_code, b.vendor_code,b.item_code,b.item_name,b.vendor_name); 该语句一直提示‘缺少关键字’
不止如何是好
求助
其中,pm_vendor_item_relation_def_t和srm_vendor_credit表的键值均为company_code, vendor_code,item_code。
on (a.company_code = b.company_code
and a.vendor_code = b.vendor_code
and a.item_code = b.item_code)
when matched then
update set a.vendor_name = b.vendor_name,a.item_name=b.item_name;
when not matched then
insert--(a.company_code, a.vendor_code,a.item_code,a.item_name,a.vendor_name)
values(b.company_code, b.vendor_code,b.item_code,b.item_name,b.vendor_name); 该语句一直提示‘缺少关键字’
不止如何是好
求助
其中,pm_vendor_item_relation_def_t和srm_vendor_credit表的键值均为company_code, vendor_code,item_code。
using (select company_code, vendor_code,item_code,item_name,vendor_name from srm_vendor_credit) b
on
a.company_code = b.company_code
and a.vendor_code = b.vendor_code
and a.item_code = b.item_code
when matched then
update pm_vendor_item_relation_def_t a set a.vendor_name = b.vendor_name,a.item_name=b.item_name;
when not matched then
insert into pm_vendor_item_relation_def_t a (a.company_code, a.vendor_code,a.item_code,a.item_name,a.vendor_name)
values(b.company_code, b.vendor_code,b.item_code,b.item_name,b.vendor_name);
--是不是这句多了个;
/*語法:
MERGE [INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;--中间没有任何标点符号
2 USING newproducts np
3 ON (p.product_id = np.product_id)
4 WHEN MATCHED THEN
5 UPDATE
6 SET p.product_name = np.product_name,
7 p.category = np.category
8 DELETE WHERE (p.category = 'ELECTRNCS')
9 WHEN NOT MATCHED THEN
10 INSERT
11 VALUES (np.product_id, np.product_name, np.category)
SQL> / 4 rows merged.去掉update后面那个“;”就ok啦