Title: Adding to a value when 'x' detected in MySQL
Tags: mysql;sql
Question: I have two tables
test1:
name: apple
count: 0
name: pear
count: 0
test2:
liked: yes
itemid: 1
liked: yes
itemid: 1
liked: yes
itemid: 2
Every time test2.itemid matches test1.id I want to add 1 to test1.count. In the instance above, apple would have a count of 2.
I have tried the query below,
UPDATE test1
LEFT JOIN
test2
ON      test2.`itemid` = test1.`id`
SET     test1.`count` = test1.`count` + 1
WHERE   test2.`liked` = 'yes';
and this happens:
test1:
name: apple
count: 1
name: pear
count: 1
As you can see it hasn't worked properly. I am guessing it just ran once for each row in test1. Any ideas?

Here is the accepted answer: UPDATE test1 t1
SET `count` = (SELECT COUNT(*)
FROM test2 t2
WHERE t1.id = t2.itemid
AND liked = 'yes');

Here is another answer: I'd sum the test2 values, then add. Also note that if you only care about test1 rows with "likes" in test2, that's an inner join:
UPDATE test1
INNER JOIN (
SELECT itemid, COUNT(*) AS valueToAdd
FROM test2
WHERE liked = 'yes'
GROUP BY itemid) test2Counts
ON test1.id = test2Counts.itemid
SET test1.`count` = test1.`count` + test2Counts.valueToAdd;
Comment for this answer: No problem - I also like Olli's answer for its simplicity. If you have a very large data set then consider mine; chances are it'll be a bit faster (just one scan through `test2` instead of once for each row in `test1`), but if the data set is relatively small there's no need to trade speed for readability.
Comment for this answer: This works great, personally I would prefer to use Olli's answer due to simplicity. Thanks for the inner join tip also, didn't know that :P

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2016:00:02/27/16', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><sql>', 'Title': "Adding to a value when 'x' detected in MySQL", 'ViewCount': 27, 'id': 35674483, 'user_AboutMe': "<p>Mostly work on web based projects but capable of working with many other frameworks/languages. </p>\n\n<p>I guess that's it. I also design random stuff, whether it be for fun or projects I'm working on.</p>\n", 'user_AccountId': 6234136.0, 'user_DisplayName': 'Ryan Butterworth', 'user_Reputation': 353.0, 'user_Views': 73.0, 'user_id': 4849946.0}}