I have a SQL table which consists of employee details and I have 3 update statements that works sepa-CodePudding

I have a SQL table which consists of employee details and my problem is I have three separate updates statements and want to club it together so that I can create a procedure which updates the table in such a way that it shows the salary including the bonus and the latest salary date which makes use of another bonus table

1]update employee_1 set employee_1.salary=(select sum(bonus_1.bonus_amount)from bonus_1 where employee_1.employee_id=bonus_1.employee_id GROUP by bonus_1.employee_id);

2]update employee_1 set employee_1.last_bonus_date=(select max(bonus_1.bonus_date)from bonus_1 where employee_1.employee_id=bonus_1.employee_id GROUP by bonus_1.employee_id);

3]update employee_1 set salary=salary old_salary;

Basically need to combine these update statements so that I can use it in a procedure

CodePudding user response：

You should've posted tables' description and sample data that illustrate the problem.

To me, it looks as if a single MERGE statement does the whole job (i.e. combines your 3 queries into 1):

merge into employee_1 a
  using bonus_1 b
  on (a.employee_id = b.employee_id)
  when matched then update set
    a.salary = a.salary   b.bonus_amount,
    a.last_bonus_date = b.bonus_date;

CodePudding user response：

You can use a MERGE statement and perform the aggregation in the USING clause:

MERGE INTO employee_1 e
USING (
  SELECT employee_id,
         SUM(bonus_amount) AS total_bonus,
         MAX(bonus_date) AS last_date
  FROM   bonus_1
  GROUP BY employee_id
) b
ON (e.employee_id = b.employee_id)
WHEN MATCHED THEN
  UPDATE
  SET salary          = e.salary   b.total_bonus,
      last_bonus_date = b.last_date;