update max id_post only from table in job crons, mysql

I have a table posts

 id_post   |  status
  3434        0
  5655        3
  5935        5
  9867        1 
  9990        3 
  10975       5
  11234       3

Im running a job cron to update every four hours the posts with status 3 but one by one only.

So,

the first time, update the post 5655 status from 3 to 1

the second time, update the post 9990 status from 3 to 1

the third time, update the post 11234 status from 3 to 1

Im thinking using, but Im not sure of it, because I canno test it locally. (the cron jobs was tested with another sql script in the server and works good)

Im thinking using, but im not sure

UPDATE posts
SET    status=1
WHERE  status = 3
ORDER BY id_post ASC
LIMIT 1

I just need some confirmation that this sql statement is going to work good one I run it in the cron job on the server. thak you.

Answer

Yes, your query will work as intended in MySQL. Unlike other databases, MySQL supports order by and limit in update statements. Their behavior is described in the documentation:

The WHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated.

So your query, as it is, would pick the “first” row with status = 3 (as defined by ordering column id), and change the status to 1. The next time it executes, the “next” row with status = 3 is picked and updated.

Beware, however, that if the content of the table is modified in between consequent executions of the queries (that is, if a row with an earlier id is inserted – or changed – with status = 1), you might not get the exact behavior that you expect.