CODEWITHSUNDEEP
mysqlinsert-update
superscral
superscral

Reputation: 480

INSERT INTO ... SELECT ... ON DUPLICATE KEY

I have a table like this:

 CREATE TABLE `UserTmp` (
`user_id` bigint(20) unsigned NOT NULL,
`nb_invit` bigint(20) unsigned DEFAULT '0',
`nb_share` bigint(20) unsigned DEFAULT '0',
`nb_sent` bigint(20) unsigned DEFAULT '0',
`total` bigint(20) unsigned DEFAULT '0',
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

And i want to execute 3 queries like this (one per table in order to have nb_share, nb_invit and nb_sent) : 

INSERT INTO UserTmp (user_id, nb_share)
  select user_id, count(share_date) as 'nb_share' 
  from Share 
  where share_date between '2012-05-21 00:00:00' and '2012-05-28 00:00:00'
ON DUPLICATE KEY UPDATE nb_share=VALUES(nb_share);

with the nb_share field is equals to the number of share of the curent user which exists in table and require an UPDATE

The goal is to have the list of users with them numbers of actions and a total field which is the sum of actions done by an user.

VALUES(nb_share) seams to give to me the total of nb_share by all users and not by the current user.

Have you an idea to solve this?

Thanks

Upvotes: 0

Views: 1892

Answers (2)

Engvard
Engvard

Reputation: 86

Or you can use a user-defined variable:

INSERT INTO UserTmp (user_id, nb_share)
  SELECT   user_id, @nbshare := count(share_date)
  FROM     Share
  WHERE    share_date BETWEEN '2012-05-21 00:00:00' AND '2012-05-28 00:00:00'
  GROUP BY user_id
ON DUPLICATE KEY UPDATE nb_share = @nbshare

Upvotes: 0

eggyal
eggyal

Reputation: 125865

You need to group the SELECT by user_id:

INSERT INTO UserTmp (user_id, nb_share)
  SELECT   user_id, count(share_date)
  FROM     Share
  WHERE    share_date BETWEEN '2012-05-21 00:00:00' AND '2012-05-28 00:00:00'
  GROUP BY user_id
ON DUPLICATE KEY UPDATE nb_share = VALUES(nb_share)

Upvotes: 3

Related Questions