I am trying to update a table on joining them in order to give 2% bonus to the person who hasn'-CodePudding

2 tables employee_1 and bonus_1 joined and trying to update but gives me the error -

ERROR REPORT UNKNOWN COMMAND

UPDATE(
  SELECT e.first_name,
         sum(b.bonus_amount) as bon
  from   employee_1 e
         LEFT join bonus_1 b
         on e.employee_id=b.employee_id
  group by e.first_name
)
set bon=0.2*e.salary
where bon IS NULL;

Please help me update this join query to give 2% bonus to employees whose bonus amount will be null in bonus_amount.

CodePudding user response：

It would help if you posted sample data.

With tables I imagine you have:

SQL> select * from employee_1;

EMPLOYEE_ID     SALARY
----------- ----------
          1        100
          2        200

SQL> select * From bonus_1;

EMPLOYEE_ID BONUS_AMOUNT
----------- ------------
          1           20
          2       --> no bonus for this employee, so their salary should be raised by 2%

One option might be merge:

SQL> merge into employee_1 e
  2    using (select b.employee_id,
  3                  sum(b.bonus_amount) as bon
  4           from bonus_1 b
  5           group by b.employee_id
  6          ) x
  7    on (x.employee_id = e.employee_id)
  8    when matched then update set
  9      e.salary = e.salary * 1.02
 10    where x.bon is null;

1 row merged.

SQL> select * from employee_1;

EMPLOYEE_ID     SALARY
----------- ----------
          1        100
          2        204

Another might be update:

SQL> rollback;

Rollback complete.

SQL> merge into employee_1 e
  2    using (select b.employee_id,
  3                  sum(b.bonus_amount) as bon
  4           from bonus_1 b
  5           group by b.employee_id
  6          ) x
  7    on (x.employee_id = e.employee_id)
  8    when matched then update set
  9      e.salary = e.salary * 1.02
 10    where x.bon is null;

1 row merged.

SQL> select * From employee_1;

EMPLOYEE_ID     SALARY
----------- ----------
          1        100
          2        204

SQL>

CodePudding user response：

If you just want to display the amount then do not use UPDATE, just use SELECT and COALESCE:

SELECT MAX(e.first_name) AS first_name,
       COALESCE(
         SUM(b.bonus_amount),
         0.2*MAX(e.salary)
       ) as bon
FROM   employee_1 e
       LEFT JOIN bonus_1 b
       ON e.employee_id=b.employee_id
GROUP BY e.employee_id;

Note: do not GROUP BY e.first_name as you will aggregate together people with the same first name; which is almost certainly incorrect.

If you want to modify the bonus_1 table to add a bonus when there are no bonuses for an employee then you want an INSERT statement and not an UPDATE:

INSERT INTO bonus_1 (employee_id, bonus_amount)
SELECT employee_id, 0.2*salary
FROM   employee_1 e
WHERE  NOT EXISTS (SELECT 1 FROM bonus_1 b WHERE b.employee_id = e.employee_id);

Or a MERGE ... WHEN NOT MATCHED THEN INSERT ... statement:

MERGE INTO bonus_1 b
USING employee_1 e
ON (b.employee_id = e.employee_id)
WHEN NOT MATCHED THEN
  INSERT (employee_id, bonus_amount)
  VALUES (e.employee_id, 0.2*e.salary);

Note: your title states a 2% bonus, if that is the case then 0.02 = 2% whereas 0.2 = 20%.

Once you have performed the insert then:

SELECT MAX(e.first_name) AS first_name,
       sum(b.bonus_amount) as bon
FROM   employee_1 e
       LEFT JOIN bonus_1 b
       ON e.employee_id=b.employee_id
GROUP BY e.employee_id;

Will have a row for all employees to display the bonuses.

fiddle