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'
Asked
Active
Viewed 2,279 times
1
Erwin Brandstetter
- 605,456
- 145
- 1,078
- 1,228
Cesar Quevedo Vega
- 33
- 5
-
What's the purpose of your query? what are you trying to achieve? – IndieTech Solutions Mar 27 '15 at 00:27
-
update the first 10 records – Cesar Quevedo Vega Mar 27 '15 at 00:28
-
1So update your question with that information (click on "edit") and add your version of Postgres, please – Erwin Brandstetter Mar 27 '15 at 00:33
1 Answers
5
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:
Erwin Brandstetter
- 605,456
- 145
- 1,078
- 1,228
-
1This is such a great answer, thank you - especially for adding the links to other answers with additional detail (and even more links!). The `FROM from_item` was a bit hard for me to understand the point of from the docs, so this was super helpful. – kevlarr Jun 27 '20 at 03:42