I have gone through several posts but unable to find the exact fix. Here is what I want -
Table1 - column1, column2, column3, amount as column4
Table2 - column1, column2, column3, amount as column4
I am using below code which works fine for insert, but for update, it doesnt update amount column
Below is the code
INSERT INTO table2 (column1,column2, column3, amount)
SELECT column1, column2, column3, sum(amount)
FROM table1
where column5 != '' and column5 != 'null' and column7 = 'good'
group by column1
ON DUPLICATE KEY UPDATE amount=SELECT sum(amount) FROM table1 where column5 != '' and column5 != 'null' and column7 = 'good' group by column1;
I know the error is for update part because when I make ON DUPLICATE KEY UPDATE amount=0 this works and all the rows are updated as 0
What am I doing wrong for update part.
Thank you for the help in advance.
I want update query part to get worked.
The subquery is not allowed on ON DUPLICATE KEY UPDATE, here is a way to do it without recalculating the total amount by using a derived table that enables references to columns from GROUP BY queries :
INSERT INTO table2 (column1,column2, column3, amount)
SELECT *
FROM (
SELECT column1, column2, column3, SUM(amount) AS _amount
FROM table1
WHERE column5 != '' and column5 != 'null' and column7 = 'good'
GROUP BY column1, column2, column3
) AS S
ON DUPLICATE KEY UPDATE amount = _amount;