Mysql INSERT INTO .. ON DUPLICATE KEY UPDATE

Question

I'm trying to do the following query in mysql 5.5

INSERT INTO countingTable( image_count, article_id ) 
SELECT COUNT( article_id ) AS sum, article_id
FROM imageTable 
ON DUPLICATE KEY UPDATE image_count = VALUES(sum)

But this gives the error:

#1054 - Unknown column 'sum' in 'field list'

Edit for better explaining what i like to do:

countingTable structure:

CREATE TABLE IF NOT EXISTS `countigTable` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `plakat` varchar(255) COLLATE utf8_bin DEFAULT NULL,
 `image_count` bigint(20) DEFAULT NULL,
 `trailer_count` bigint(20) DEFAULT NULL,
 `actor_count` bigint(20) DEFAULT NULL,
 .... (many more counting fields)
 `article_id` bigint(20) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `article_id` (`article_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

imageTable has the structure:

CREATE TABLE IF NOT EXISTS `imageTable` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `article_id` bigint(20) DEFAULT NULL,
 `image_id` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `article_id` (`article_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

(imageTable is basically a referencing table for many2many relation)

The countingTable already has rows referencing the article_id. Now i like to count all related images and insert or update that in the countingTable.

Later i need the same stuff for trailer_count, actor_count and so on.

If this is done the first time, the countingTable will be updated by triggers.

The idea is, to have this table so I dont't need to join all relations just for counting if they exist. (As my project needs the countings all the time)

Answer 1

Use UPDATE image_count = VALUES(image_count).

VALUES() expects a name from the columns you are inserting into, not the alias in the query. The query should be:

INSERT INTO countingTable (image_count, article_id) 
SELECT COUNT(article_id) AS sum, article_id
FROM imageTable 
GROUP BY article_id -- I suppose you skipped that line?
ON DUPLICATE KEY UPDATE image_count = VALUES(image_count) ;

If you want the new values to be added to the existing ones, use:

...
ON DUPLICATE KEY UPDATE image_count = image_count + VALUES(image_count) ;

• Evenbit GmbH: Thanks. That works somehow but does not do what i want to do. I like to have the sum value updated on rows that already have this artcle_id and a new row it there is no row with this article_id. Your tip doesn't do that. But it fixes the error in my query.
• ypercubeᵀᴹ: You want the two values (existing and new) added in that case? See the edit.
• ypercubeᵀᴹ: And I think you missed the GROUP BY article_id line from the query.
• Evenbit GmbH: I have edited the question. Perhaps it is more clear now what i like to get.
• Evenbit GmbH: Argghhh. You are right. I forgot the GROUP BY. Did that and everything worked as expected. Thank you!