Mysql INSERT INTO .. ON DUPLICATE KEY UPDATE

MySQLmysql-5.5

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)

Best Answer

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) ;
Related Question