Insert or update column from one mySQL table to another error

Question

I have the table behaviour where I count the pages of each hash. I want to transfer the results into a new table new_table. If the hash on new_table exists, then just update the number of pages. Otherwhise, if it is a new hash found on behaviour that did not inserted yet to new_table just add it.

INSERT INTO new_table (hash, pages)
SELECT hash, COUNT( id ) AS pages
FROM behaviour
ON DUPLICATE KEY UPDATE new_table.pages=behaviour.pages
GROUP BY hash

I get an error on the 5th line "group by hash". What am I doing wrong?

#1064 - 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 hash' at line 5

Maybe you can use "Replace Into" http://stackoverflow.com/questions/19820724/replace-into-query-syntax — EllisTheEllice, Apr 26 '16 at 09:10

score 0 · Answer 1 · answered Apr 26 '16 at 09:09

0

INSERT INTO new_table (hash, pages)
SELECT hash, COUNT( id ) AS pages
FROM behaviour
ON DUPLICATE KEY UPDATE new_table.pages=behaviour.pages
GROUP BY hash;

Try this

answered Apr 26 '16 at 09:09

Priyanshu

885
6
12

I added ' ; ' (semicolon) at the end. still the same error ?? – Priyanshu Apr 26 '16 at 09:12
maybe the problem is because the COUNT( id ) AS pages from behaviour is not passed to the new_table.pages as a number – villoui Apr 26 '16 at 09:23

Andrews B Anthony · Accepted Answer · 2016-04-26T09:26:14.060

0

Use the below query. This will work

INSERT INTO new_table (hash, pages)
SELECT hash, COUNT( id ) AS pages
FROM behaviour GROUP BY hash
ON DUPLICATE KEY UPDATE pages=VALUES(pages)

You Must add the group by clause before the on duplicate key check

edited Apr 26 '16 at 09:26

answered Apr 26 '16 at 09:20

Andrews B Anthony

1,381
9
27

maybe the problem is because the COUNT( id ) AS pages from behaviour is not passed to the new_table.pages as a number – villoui Apr 26 '16 at 09:23

Insert or update column from one mySQL table to another error

2 Answers2