SQL Error [42601]: ERROR: syntax error at or near “limit”

postgresql

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?

Best Answer

LIMIT isn't a valid keyword in an UPDATE statement according to the official PostgreSQL documentation:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Reference: UPDATE (PostgreSQL Documentation )

Solution

Remove LIMIT 10 from your statement.

Related Question