CODEWITHSUNDEEP
mysqlgroup-bysql-updatesql-insert
user3127882
user3127882

Reputation: 522

INSERT FROM SELECT UPDATE ON DUPLICATES UPDATE = count(*)

Updating the count of frequent terms in a dictionary for some corpus. Unable to do in a single query. Instead, I gather count data into a temporary table and then INSERT/UPDATE the dictionary. Wondering what the proper syntax would be to UPDATE countInCorpus in a single command.

Current syntax:

INSERT INTO temp_table (name, countInCorpus)
    SELECT name,count(*) AS theCount
    FROM corpus
    GROUP BY name having theCount > 9);

INSERT INTO dict (name, countInCorpus)
    SELECT name, countInCorpus
    FROM temp_table ON DUPLICATE KEY UPDATE dict.countInCorpus=temp_table.countInCorpus;

Failing one-step syntax (resulting in "unknown column 'theCount' in field list") :

INSERT INTO dict (name, countInCorpus) 
    SELECT name,count(*) AS theCount
    FROM corpus
    GROUP BY name having theCount > 9
    ON DUPLICATE KEY UPDATE dict.countInCorpus=theCount;

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use VALUES():

INSERT INTO dict (name, countInCorpus) 
    SELECT name,count(*) AS theCount
    FROM corpus
    GROUP BY name havingtheCount > 9
    ON DUPLICATE KEY UPDATE dict.countInCorpus = VALUES(countInCorpus);

Upvotes: 1

Related Questions