UPDATE order in PostgreSQL

I have a mysql command:

 update table_demo SET flag= 1 where flag=0 ORDER BY id ASC LIMIT 10

and need the same command in Postgres, I get this error:

ERROR: syntax error at or near 'ORDER'

Answer

To update 10 first rows (that actually need the update):

UPDATE table_demo t
SET    flag = 1
FROM  (
   SELECT table_demo_id   -- use your actual PK column(s)
   FROM   table_demo
   WHERE  flag IS DISTINCT FROM 1
   ORDER  BY id
   LIMIT  10
   FOR    UPDATE
   ) u
WHERE  u.table_demo_id = t.table_demo_id;

FOR UPDATE (row level locks) are only needed to protect against concurrent write access. If your transaction is the only one writing to that table, you don’t need it.

If flag is defined NOT NULL, you can use WHERE flag <> 0.

Related answers with more explanation and links:

• Update top N values using PostgreSQL

• How do I (or can I) SELECT DISTINCT on multiple columns?