1

I have the query of

INSERT IGNORE INTO t2 (ID,P1,P2,Items) 
    SELECT ID,P1,P2,SUM(Items) 
    FROM t1 GROUP BY ID,P1,P2;

Since I need to run this query frequently to inspect changes in t1, I need to add

ON DUPLICATE KEY UPDATE t2.Items=SUM(t1.Items)

but it gives ERROR 1111 (HY000): Invalid use of group function error.

0

Like all functions you need to make a further select with the data

And please next time provide a minimal reproducible example, so that the answer can be doe quicker and with much less effort

CREATE TABLe t2 (ID INT primary key,P1 INT,P2 INT ,Items INT ) 
INSERT INTO t2 VALUES (1,1,1,1)
CREATE TABLe t1 (ID INT primary key,P1 INT,P2 INT ,Items INT ) 
INSERT INTO t1 VALUES (1,1,2,2)
INSERT IGNORE INTO t2 (ID,P1,P2,Items) 
    SELECT ID,P1,P2, t1sum
    FROM
    (SELECT ID,P1,P2,SUM(Items) t1sum
    FROM t1 GROUP BY ID,P1,P2) t3
ON DUPLICATE KEY UPDATE t2.Items=t3.t1sum
SELECT * FROM t2
ID | P1 | P2 | Items
-: | -: | -: | ----:
 1 |  1 |  1 |     2

db<>fiddle here

1
  • the hit of the first line was enough! Thanks! – Googlebot May 9 at 16:15
0

On DUPLICATE KEY UPDATE you can use the VALUES(columnname) syntax to access the value of the column to be inserted:

INSERT INTO t2 (ID, P1, P2, Items) 
    SELECT ID, P1, P2, SUM(Items) 
    FROM t1 
    GROUP BY ID,P1,P2
ON DUPLICATE KEY UPDATE t2.Items = VALUES(Items) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.