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)

7

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) ;
| improve this answer | |
  • 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. – Evenbit GmbH Aug 6 '14 at 16:47
  • You want the two values (existing and new) added in that case? See the edit. – ypercubeᵀᴹ Aug 6 '14 at 16:49
  • And I think you missed the GROUP BY article_id line from the query. – ypercubeᵀᴹ Aug 6 '14 at 16:53
  • I have edited the question. Perhaps it is more clear now what i like to get. – Evenbit GmbH Aug 6 '14 at 17:01
  • Argghhh. You are right. I forgot the GROUP BY. Did that and everything worked as expected. Thank you! – Evenbit GmbH Aug 6 '14 at 17:06

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.