How to resolve the error: SQL state 42601?
When I am using this command to update table in PostgreSQL 13:
UPDATE rss_sub_source
SET sub_url = SUBSTRING(sub_url, 1, CHAR_LENGTH(sub_url) - 1)
WHERE sub_url LIKE '%/'
limit 10
but shows this error:
SQL Error [42601]: ERROR: syntax error at or near "limit"
Position: 111
Why would this error happen and what should I do to fix it?
- To resolve the error: SQL state 42601-
- You could make something like this
But a Limit without an ORDER BY makes no sense, so you must choose one that gets you the correct 10 rows
UPDATE rss_sub_source t1
SET t1.sub_url = SUBSTRING(t1.sub_url, 1, CHAR_LENGTH(t1.sub_url) - 1)
FROM (SELECT id FROM rss_sub_source WHERE sub_url LIKE '%/' ORDER BY id LIMIT 10) t2
WHERE t2.id = t1.id