I am trying to do a massive PostgreSQL update and can not figure out the proper way

Question

    UPDATE member_copy
SET locked_date = CURRENT_DATE,
 notes = (
    SELECT
        notes
    FROM
        member_copy
    WHERE
        ID = 17579
) || ' CLOSED'
WHERE
    ID = 17579

I am trying to run a database update just like this in PostgreSQL 8.1.23. I need to do this update with about 800 ids at the same time. Any help would be appreciated.

Answer 1

You can largely simplify to:

UPDATE member_copy
SET locked_date = CURRENT_DATE
   ,notes = COALESCE(notes || ' ', '') || 'CLOSED'
WHERE id IN (17579, 12345, ...); --- 800 IDs

COALESCE is meant to catch cases where notes IS NULL. Should even work in ancient Postgres 8.1. But you really must upgrade to a current version.

In modern day Postgres I would use a temp table of IDs (or a subselect) and join to it in the UPDATE.