CODEWITHSUNDEEP
mysqlsqlgroup-bysql-update
sweetu514
sweetu514

Reputation: 65

Update with group by Clause in Mysql

I have this Employee_Detail table which looks something like below

 ID| EMP_NO| EMP_NAME| YEARMONTH| CURR_MTH_PAY_AMT| TOTAL_CURR_MTH_PAY_AMT| CURR_MTH_DED_AMT| TOTAL_CURR_MTH_DED_AMT
  1| 1      | 'Alan'  | 201712   | 100             | NULL                  |  50             | NULL
  2| 1      | 'Alan'  | 201712   | 200             | NULL                  |  70             | NULL
  3| 1      | 'Alan'  | 201712   | 300             | NULL                  |  80             | NULL
  4| 1      | 'Alan'  | 201801   | 1000            | NULL                  | 500             | NULL
  5| 1      | 'Alan'  | 201801   | 2000            | NULL                  | 700             | NULL
  6| 1      | 'Alan'  | 201801   | 3000            | NULL                  | 800             | NULL
  7| 1      | 'Alan'  | 201801   | 4000            | NULL                  | 700             | NULL
  8| 2      | 'Bob'   | 201712   | 400             | NULL                  | 50              | NULL
  9| 2      | 'Bob'   | 201712   | 500             | NULL                  | 60              | NULL
 10| 2      | 'Bob'   | 201712   | 600             | NULL                  | 70              | NULL
 11| 2      | 'Bob'   | 201802   | 700             | NULL                  | 70              | NULL
 12| 2      | 'Bob'   | 201802   | 800             | NULL                  | 80              | NULL
 13| 2      | 'Bob'   | 201802   | 900             | NULL                  | 90              | NULL
 14| 2      | 'Bob'   | 201802   | 900             | NULL                  | 90              | NULL

I am trying to update TOTAL_CURR_MTH_PAY_AMT as SUM(CURR_MTH_PAY_AMT) and TOTAL_CURR_MTH_DED_AMT as SUM(CURR_MTH_DED_AMT) for each employee for each yearmonth, so that my table looks something like below.

ID| EMP_NO| EMP_NAME| YEARMONTH| CURR_MTH_PAY_AMT| TOTAL_CURR_MTH_PAY_AMT| CURR_MTH_DED_AMT| TOTAL_CURR_MTH_DED_AMT
  1| 1      | 'Alan'  | 201712   | 100             | 600                  |  50             | 200
  2| 1      | 'Alan'  | 201712   | 200             | 600                  |  70             | 200
  3| 1      | 'Alan'  | 201712   | 300             | 600                  |  80             | 200
  4| 1      | 'Alan'  | 201801   | 1000            |10000                 | 500             | 2700
  5| 1      | 'Alan'  | 201801   | 2000            |10000                 | 700             | 2700
  6| 1      | 'Alan'  | 201801   | 3000            |10000                 | 800             | 2700
  7| 1      | 'Alan'  | 201801   | 4000            |10000                 | 700             | 2700
  8| 2      | 'Bob'   | 201712   | 400             | 1500                 | 50              | 180
  9| 2      | 'Bob'   | 201712   | 500             | 1500                 | 60              | 180
 10| 2      | 'Bob'   | 201712   | 600             | 1500                 | 70              | 180
 11| 2      | 'Bob'   | 201802   | 700             | 3300                 | 70              | 330
 12| 2      | 'Bob'   | 201802   | 800             | 3300                 | 80              | 330
 13| 2      | 'Bob'   | 201802   | 900             | 3300                 | 90              | 330
 14| 2      | 'Bob'   | 201802   | 900             | 3300                 | 90              | 330

I have tried using this query on Mysql

update Employee_Detail set TOTAL_CURR_MTH_PAY_AMT = SUM(CURR_MTH_PAY_AMT) , TOTAL_CURR_MTH_DED_AMT = SUM(CURR_MTH_DED_AMT)
group by YYYYMM, EMP_NO;

But it gives me this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by YEARMONTH, EMP_NO'.

I am unable to use the group by clause with update. Any help would be much appreciated.

Upvotes: 2

Views: 2966

Answers (1)

Blank
Blank

Reputation: 12378

You can not use group by for update, instead, you can try this:

update Employee_Detail
join (
    select EMP_NO, YEARMONTH, sum(CURR_MTH_PAY_AMT) TOTAL_CURR_MTH_PAY_AMT, SUM(CURR_MTH_DED_AMT) TOTAL_CURR_MTH_DED_AMT
    from Employee_Detail
    group by EMP_NO, YEARMONTH
) t on Employee_Detail.EMP_NO = t.EMP_NO AND Employee_Detail.YEARMONTH = T.YEARMONTH
set Employee_Detail.TOTAL_CURR_MTH_PAY_AMT = t.TOTAL_CURR_MTH_PAY_AMT,
Employee_Detail.TOTAL_CURR_MTH_DED_AMT = t.TOTAL_CURR_MTH_DED_AMT

Subquery will fetch total CURR_MTH_PAY_AMT and total CURR_MTH_DED_AMT for each group, then join Employee_Detail to update each record.

SQLFiddle DEMO here.

Upvotes: 5

Related Questions