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'
-
What's the purpose of your query? what are you trying to achieve?IndieTech Solutions– IndieTech Solutions2015-03-27 00:27:06 +00:00Commented Mar 27, 2015 at 0:27
-
update the first 10 recordsCesar Quevedo Vega– Cesar Quevedo Vega2015-03-27 00:28:25 +00:00Commented Mar 27, 2015 at 0:28
-
1So update your question with that information (click on "edit") and add your version of Postgres, pleaseErwin Brandstetter– Erwin Brandstetter2015-03-27 00:33:06 +00:00Commented Mar 27, 2015 at 0:33
Add a comment
|
1 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:
answered Mar 27, 2015 at 0:36
Erwin Brandstetter
668k160 gold badges1.2k silver badges1.3k bronze badges
1 Comment
kevlarr
This 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.