alter proc UpdateOrder
(
@Quantity int,
@ProductID int,
@OrderID int
)
as
begin
declare @num int
select @num=Quantity from [Order Details] where OrderID=@OrderID and ProductID=@ProductID
if @@rowcount > 0
Update [Order Details] set Quantity=@num+@Quantity where OrderID=@OrderID and ProductID=@ProductID
else
Insert into [Order Details] (Quantity,ProductID,OrderID) values (@Quantity,@ProductID,@OrderID)
end
print @num
go
(
@Quantity int,
@ProductID int,
@OrderID int
)
as
begin
declare @num int
select @num=Quantity from [Order Details] where OrderID=@OrderID and ProductID=@ProductID
if @@rowcount > 0
Update [Order Details] set Quantity=@num+@Quantity where OrderID=@OrderID and ProductID=@ProductID
else
Insert into [Order Details] (Quantity,ProductID,OrderID) values (@Quantity,@ProductID,@OrderID)
end
print @num
go
merge语句实现
Purpose
Use the MERGE statement to select rows from one table for update or insertion into another table. The decision whether to update or insert into the target table is based on a condition in the ON clause.This statement is a convenient way to combine at least two operations. It lets you avoid multiple INSERT and UPDATE DML statements.MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.Prerequisites
You must have INSERT and UPDATE object privileges on the target table and SELECT privilege on the source table.
Examples
Merging into a Table: Example
The following example creates a bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales (based on the sales_rep_id column of the oe.orders table). Finally, the Human Resources manager decides that all employees should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);INSERT INTO bonuses(employee_id)
(SELECT e.employee_id FROM employees e, orders o
WHERE e.employee_id = o.sales_rep_id
GROUP BY e.employee_id); SELECT * FROM bonuses;EMPLOYEE_ID BONUS
----------- ----------
153 100
154 100
155 100
156 100
158 100
159 100
160 100
161 100
163 100MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);EMPLOYEE_ID BONUS
----------- ----------
153 180
154 175
155 170
156 200
158 190
159 180
160 175
161 170
163 195
157 950
145 1400
170 960
179 620
152 900
169 1000
.
.