Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables as

CREATE TABLE country_statistics
(
id int(11) NOT NULL AUTO_INCREMENT
us int(11),
uk int(11),
ca int(11),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT

CREATE TABLE entries
(
id int(11) NOT NULL AUTO_INCREMENT
list varchar(255),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT

I want to search for the occurrence of each country in the list column. I use multiple subqueries to do so

UPDATE country_statistics
us = (SELECT COUNT(*) FROM entries WHERE list LIKE '%United States%',
uk = (SELECT COUNT(*) FROM entries WHERE list LIKE '%United Kingdom%',
ca = (SELECT COUNT(*) FROM entries WHERE list LIKE '%Canada%'
WHERE id = 'xx'

How can I reduce the number of subqueries, and count all values in one subquery, then updating all columns?

share|improve this question
    
You had a similar question or rather a question with a similar answer, last year: GROUP BY two columns –  ypercube Sep 19 '13 at 12:39
add comment

1 Answer

up vote 2 down vote accepted

You can use JOIN syntax in UPDATE and conditional count in the subquery

UPDATE country_statistics s CROSS JOIN
(
  SELECT SUM(CASE WHEN list LIKE '%United States%' THEN 1 ELSE 0 END) us,
         SUM(CASE WHEN list LIKE '%United Kingdom%' THEN 1 ELSE 0 END) uk,
         SUM(CASE WHEN list LIKE '%Canada%' THEN 1 ELSE 0 END) ca
    FROM entries 
) q
   SET s.us = q.us, s.uk = q.uk, s.ca = q.ca
 WHERE id = 1

Here is SQLFiddle demo

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.