-1

I want to insert selected data from a table into another table and using 'ON DUPLICATE KEY UPDATE' to update the data , the query is >>>


INSERT INTO z_user_wallet_txn_2022_03 
          (user_id, total_deposit, total_withdraw) 
SELECT user_id,
       SUM(case when type_id = 1 then approved_coins else 0 end) as totalDeposit,
       sum(case when type_id = 2 then approved_coins else 0 end) as totalWithdraw 
from wallet_transaction
WHERE (type_id = 1 OR type_id = 2)
  AND (status = "Approved")
  AND offer_flag = 0
  AND DATE_FORMAT(approved_on,"%y-%m") = DATE_FORMAT('2022-03-01',"%y-%m") 
  AND DATE(approved_on) = '2022-05-03'
 GROUP BY user_id
 ON DUPLICATE KEY UPDATE 
       z_user_wallet_txn_2022_03.user_id = z_user_wallet_txn_2022_03.user_id,
       z_user_wallet_txn_2022_03.total_deposit = wallet_transaction.totalDeposit,
       z_user_wallet_txn_2022_03.total_withdraw = wallet_transaction.totalWithdraw;

But it shows the error >>> [1054] [42S22]: Unknown column 'wallet_transaction.totalDeposit' in 'field list'

I can't find any way to do this , I want to do this work using only this single query

O. Jones
  • 92,698
  • 17
  • 108
  • 152
Sagar Sangwan
  • 9
  • 2
  • One answer in the linked question addresses how to handle GROUP BY clauses in INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statements. tl;dr subqueries. – O. Jones Jun 02 '22 at 10:23
  • `DATE_FORMAT(approved_on,"%y-%m") = DATE_FORMAT('2022-03-01',"%y-%m") AND DATE(approved_on) = '2022-05-03'` will never be true at the same time – HoneyBadger Jun 02 '22 at 10:29

0 Answers0