MySQL UPDATE IF SELECT Query -

MySQL UPDATE IF SELECT Query -

i need build mysql query following:

select count of rows user posts table update column fields of row id = 15 if count result less or equal 5

here have tried:

update posts set post_title = 'my title' if(select count(id) posts (post_status = 'saved' , user_id = 1) <= 5) id = 15

question: possible perform these types of operations within single query?

yes, it's possible. there several ways incorporate subquery update statement.

one way utilize multi-table update, , utilize inline view homecoming count:

update posts p bring together (select count(c.id) cnt posts c c.post_status = 'saved' , c.user_id = 1 ) v on v.cnt <= 5 set p.post_title = 'my title' p.id = 15 limit 1

the inline view query (aliased v) run first. homecoming 1 row (because of count aggregate). utilize bring together operation match rows in posts table (aliased p).

for bring together predicate, reference returned "count" inline view. if that's greater five, won't match rows posts table, no rows updated.

there several ways obtain same result:

update ( select count(c.id) cnt posts c c.post_status = 'saved' , c.user_id = 1 having count(c.id) <= 5 ) v bring together posts p on p.id = 15 set p.post_title = 'my title' limit 1

or

update posts p set p.post_title = 'my title' p.id = 15 , ( select count(c.id) cnt posts c c.post_status = 'saved' , c.user_id = 1 ) <= 5 limit 1

mysql