Merge intoの使い方
Merge , Merge into , Merge into , update , Merge into . merge SQL inserts updates . update insert ,Merge into B A , A , B A . MERGE updating inserting .
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATHED THEN [execute something else here ! ]
merge into products p using newproducts np on (p.product_id = np.product_id)
when matched then
update set p.product_name = np.product_name
when not matched then
insert values(np.product_id, np.product_name, np.category)
。 merger into products using newproducts newproducts merge products ,merge on , product_id , when matched then , update set p.product_name = np.product_name, newproduct , product product_name 。 insert 。 merget inot 。 merger , , , , merge , ,merge update/insert 。 merge , 。
using 。 newproducts
merge into products p using (select * from newproducts) np on (p.product_id = np.product_id)
when matched then
update set p.product_name = np.product_name
when not matched then
insert values(np.product_id, np.product_name, np.category)
MERGE :
1、UPDATE INSERT
2、UPDATE INSERT WHERE
3、 ON insert ,
4、UPDATE DELETE
1. UPDATE INSERT
update insert
merge into products p using newproducts np on (p.product_id = np.product_id)
when matched then
update set p.product_name = np.product_name
, , 。
2. UPDATE INSERT WHERE
, , where , , where insert
merge into products p using (select * from newproducts) np on (p.product_id = np.product_id)
when matched then
update set p.product_name = np.product_name where np.product_name like 'OL%'
product_name 'OL' update, 'OL' ,insert where
merge into products p using (select * from newproducts) np on (p.product_id = np.product_id)
when matched then
update set p.product_name = np.product_name where np.product_name like 'OL%'
when not matched then
insert values(np.product_id, np.product_name, np.category) where np.product_name like 'OL%'
, , 。
3. ON insert ,
merge into products p using (select * from newproducts) np on (1=0)
when matched then
update set p.product_name = np.product_name
when not matched then
insert values(np.product_id, np.product_name, np.category)
, insert into , merge
4. UPDATE DELETE
delete update , where
merge into products p using (select * from newproducts) np on (p.product_id = np.product_id)
when matched then
update set p.product_name = np.product_name delete where p.product_id = np.product_id where np.product_name like 'OL%'
when not matched then
insert values(np.product_id, np.product_name, np.category)
prodcut_name product , product_name OL 。
MERGEは文法が簡単で、仕事で似たようなニーズがあれば、MERGE INTOを試してみると、予想外の効果が得られます.