0

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?

1
2

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

0

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.