Re: Updating 457 rows in a table

> On 19 May 2024, at 20:37, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> On Sun, 19 May 2024, Christophe Pettus 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 ...
>
> Ah, yes. Hadn't thought of that. The statement would be
> UPDATE people SET active=true WHERE email is not null;

That aside, while you’re not absolutely 100% definitely sure that an UPDATE or DELETE statement is going to do exactly
whatyou intended, and for good measure if you are, wrapping such statements in a transaction allows you to ROLLBACK to
getback 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
otherrows 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
oneof 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.