Subject: Re: Updating 457 rows in a table

From: Christophe Pettus <[email-masked]>
Date: Sun, 19 May 2024 09:56:50 -0700

> On May 19, 2024, at 09:54, Rich Shepard <[email-masked]> wrote:
> 
> Specifically, in the 'people' table I want to change the column 'active'
> from false to true for 457 specific person_id row numbers.

UPDATE people SET active=true WHERE id IN (...);

The ... can either be an explicit list of the ids, or a SELECT id WHERE if you have a predicate that selects the appropriate ids.

---

From: Rich Shepard <[email-masked]>
Date: Sun, 19 May 2024 11:30:30 -0700 (PDT)

On Sun, 19 May 2024, Christophe Pettus wrote:

> The ... can either be an explicit list of the ids, or a SELECT id WHERE if
> you have a predicate that selects the appropriate ids.

Christophe,

That's a good idea; I can use a predicate to identify the rows to update.
That would be shorter than a long, comma-separated list.

Thanks,

Rich

---

From: Christophe Pettus <[email-masked]>
Date: Sun, 19 May 2024 11:32:39 -0700

> On May 19, 2024, at 11:30, Rich Shepard <[email-masked]> wrote:

Of course, you can probably also shorten the query to:

	UPDATE people SET active=true WHERE ...

Where ... is the predicate you would have used in the SELECT id WHERE ...

---

From: Rich Shepard <[email-masked]>
Date: Sun, 19 May 2024 11:37:50 -0700 (PDT)

> Where ... is the predicate you would have used in the SELECT id WHERE ...

Ah, yes. Hadn't thought of that. The statement would be
 	UPDATE people SET active=true WHERE email is not null;

Thanks, Christophe,

Rich

---

From: Alban Hertroys <[email-masked]>
Date: Mon, 20 May 2024 11:30:29 +0200

> On 19 May 2024, at 20:37, Rich Shepard <[email-masked]> wrote:
> 

That aside, while you’re not absolutely 100% definitely sure that an UPDATE or DELETE statement is going to do exactly what you intended, and for good measure if you are, wrapping such statements in a transaction allows you to ROLLBACK to get back to the state that you started from.

So:
=> BEGIN;
=> UPDATE people SET active=true WHERE email is not null;
(497 rows affected)

If that does indeed read “497 rows affected”:
=> COMMIT;

But if that doesn’t read 497, instead of COMMITting the transaction, you now have the opportunity to investigate what other rows changed that shouldn’t have and how to change your predicates - and then simply type:
=> ROLLBACK;

Don’t forget to start a new transaction again for the next attempt.

In PostgreSQL this also works for almost all DDL statements (CREATE TABLE, DROP TABLE, TRUNCATE TABLE, etc.), which is one of the features about this database that I really appreciate - some big names don’t have that.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

---

From: Rich Shepard <[email-masked]>
Date: Mon, 20 May 2024 05:33:37 -0700 (PDT)

On Mon, 20 May 2024, Alban Hertroys wrote:

> That aside, while you’re not absolutely 100% definitely sure that an
> UPDATE or DELETE statement is going to do exactly what you intended, and
> for good measure if you are, wrapping such statements in a transaction
> allows you to ROLLBACK to get back to the state that you started from.

Alban,

That's excellent advice and I'll do so in all future data manipulation
scripts. In the current situation psql did return that 457 rows had been
updated.

Much appreciated,

Rich