PostgreSQL中如何将多条UPDATE语句合并为单条查询？

I have these two UPDATE queries:

update nodes set path = 'A.S' || case when nlevel(path) > nlevel('A.C') then subpath(path, nlevel('A.C')) when nlevel(path) = nlevel('A.C') then '' end where path <@ 'A.C'
update nodes set id = 'A.S' where id = 'A.C'

I want to merge them into a single statement like this (which I know doesn't work):

update nodes set path = 'A.S' || case when nlevel(path) > nlevel('A.C') then subpath(path, nlevel('A.C')) when nlevel(path) = nlevel('A.C') then '' end where path <@ 'A.C' and update nodes set id = 'A.S' where id = 'A.C'

I've looked for how to do this but haven't found anything. Is this possible? Please help, thanks.

First off, the syntax you tried (chaining two UPDATE statements with AND) is invalid in PostgreSQL—you can't structure queries that way. But you can combine both update operations into a single UPDATE statement by handling each field's logic in the SET clause with conditional checks.

Here's how to rewrite your two queries into one valid statement:

UPDATE nodes
SET 
  path = CASE 
           WHEN path <@ 'A.C' THEN 
             'A.S' || CASE 
                        WHEN nlevel(path) > nlevel('A.C') THEN subpath(path, nlevel('A.C'))
                        WHEN nlevel(path) = nlevel('A.C') THEN ''
                      END
           ELSE path -- Leave path unchanged if the condition isn't met
         END,
  id = CASE 
         WHEN id = 'A.C' THEN 'A.S'
         ELSE id -- Leave id unchanged if the condition isn't met
       END
WHERE path <@ 'A.C' OR id = 'A.C'; -- Only target rows that need updating

How this works: #

• Conditional field updates: For each field (path and id), we use a CASE statement to only apply the change if the original row meets the corresponding condition. If not, we just set the field to its current value (so no change happens).
• Filtered rows: The WHERE clause ensures we only touch rows that need either the path or id updated—this avoids unnecessary work and full-table scans.
• Overlapping rows: If a single row satisfies both conditions (i.e., it has path <@ 'A.C' and id = 'A.C'), this statement will update both fields in one go, which is probably what you want.

If you're certain the two sets of rows (those needing path updates vs id updates) don't overlap, you could split the logic with more specific conditions, but the above approach works for both overlapping and non-overlapping cases.

内容的提问来源于stack exchange，提问作者Andrey Yaskulsky