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?