I need to build a mysql query that does the following:
Here is what I have tried:
UPDATE posts SET post_title = 'my title' IF(SELECT COUNT(ID) FROM posts WHERE (post_status = 'saved' AND user_id = 1) <= 5) WHERE ID = 15
Question: Is it possible to perform these types of operations within a single query?
Yes, it's possible. There are several ways to incorporate a subquery into an UPDATE statement.
One way is to use a multi-table update, and use an inline view to return the count:
UPDATE posts p
JOIN (SELECT COUNT(c.id) AS cnt
FROM posts c
WHERE c.post_status = 'saved'
AND c.user_id = 1
) v
ON v.cnt <= 5
SET p.post_title = 'my title'
WHERE p.id = 15
LIMIT 1
The inline view query (aliased as v) is run first. This will return one row (because of the COUNT aggregate). We then use a join operation to match to rows in the posts table (aliased as p).
For the join predicate, we reference the returned "count" from the inline view. If that's greater than five, then it won't match any rows from the posts table, so no rows will be updated.
There are several ways to obtain the same result:
UPDATE ( SELECT COUNT(c.id) AS cnt
FROM posts c
WHERE c.post_status = 'saved'
AND c.user_id = 1
HAVING COUNT(c.id) <= 5
) v
JOIN posts p
ON p.id = 15
SET p.post_title = 'my title'
LIMIT 1
Or
UPDATE posts p
SET p.post_title = 'my title'
WHERE p.id = 15
AND ( SELECT COUNT(c.id) AS cnt
FROM posts c
WHERE c.post_status = 'saved'
AND c.user_id = 1
) <= 5
LIMIT 1
