How to update a table by count from another table?

Question

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?

Answer 1

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