打个标签：merge into

merge into :

 

 

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 100

 

 

 

MERGE 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

     DELETE WHERE (S.salary > 8000)

   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)

     VALUES (S.employee_id, S.salary*0.1)

     WHERE (S.salary <= 8000);

 

 

EMPLOYEE_ID      BONUS

----------- ----------

        153        180

        154        175

        155        170

        159        180

        160        175

        161        170

        179        620

        173        610

        165        680

        166        640

        164        720

        172        730

        167        620

        171        740