一条语句做不了.得判断一下. if exists (select 1 from tb where ...) update ... else insert ...
一条语句是可以做到的,这是sqlserver里面没有,而oracle自己有的 那就是merge into语句如: SQL> MERGE INTO products p 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; 具体参考: http://blog.chinaunix.net/u1/55091/showart_430716.html
用merge into没有则Insert有则update
用 MEREG INTO 就行
MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id) --如果product_id匹配,则update WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name, p.category = np.category --否则insert when not matched then insert values (np.product_id, np.product_name, np.category);
有啊,用merge into来做:SQL> MERGE INTO products p 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 WHERE p.category = np.category; 2 rows merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS CAMERA ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER DVD SQL> SQL> rollback;
学习了!~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 ! ]
if exists (select 1 from tb where ...)
update ...
else
insert ...
那就是merge into语句如:
SQL> MERGE INTO products p
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;
具体参考:
http://blog.chinaunix.net/u1/55091/showart_430716.html
用 MEREG INTO 就行
MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
--如果product_id匹配,则update
WHEN MATCHED THEN
UPDATE SET p.product_name = np.product_name, p.category = np.category
--否则insert
when not matched then
insert values (np.product_id, np.product_name, np.category);
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 WHERE p.category = np.category; 2 rows merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY
---------- -------------------- ----------
1501 VIVITAR 35MM ELECTRNCS
1502 OLYMPUS CAMERA ELECTRNCS
1600 PLAY GYM TOYS
1601 LAMAZE TOYS
1666 HARRY POTTER DVD
SQL>
SQL> rollback;