0
    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.

inimrepus
  • 69
  • 3
  • 8
  • 4
    You should plan for an upgrade to a supported version *now*. 8.1 has been out of maintenance for years now. –  Dec 10 '13 at 15:56
  • I would love to, but that isn't my call. – inimrepus Dec 10 '13 at 15:58
  • 2
    Surely you can at least call out the potential for security issues that may arise from not doing so. Perhaps I've been privileged, but I've yet to meet a manager who sweeps that kind of problem under a rug. – Denis de Bernardy Dec 10 '13 at 16:22

1 Answers1

2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you! This worked perfectly for me. Unfortunately upgrading isn't my call, but there is a new site planned for soon. – inimrepus Dec 10 '13 at 21:36