public inbox for [email protected]
help / color / mirror / Atom feedRe: Updating 457 rows in a table
6+ messages / 3 participants
[nested] [flat]
* Re: Updating 457 rows in a table
@ 2024-05-19 16:56 Christophe Pettus <[email protected]>
2024-05-19 18:30 ` Re: Updating 457 rows in a table Rich Shepard <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Christophe Pettus @ 2024-05-19 16:56 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: [email protected]
> On May 19, 2024, at 09:54, Rich Shepard <[email protected]> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Updating 457 rows in a table
2024-05-19 16:56 Re: Updating 457 rows in a table Christophe Pettus <[email protected]>
@ 2024-05-19 18:30 ` Rich Shepard <[email protected]>
2024-05-19 18:32 ` Re: Updating 457 rows in a table Christophe Pettus <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Rich Shepard @ 2024-05-19 18:30 UTC (permalink / raw)
To: [email protected]
On Sun, 19 May 2024, Christophe Pettus wrote:
> 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.
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Updating 457 rows in a table
2024-05-19 16:56 Re: Updating 457 rows in a table Christophe Pettus <[email protected]>
2024-05-19 18:30 ` Re: Updating 457 rows in a table Rich Shepard <[email protected]>
@ 2024-05-19 18:32 ` Christophe Pettus <[email protected]>
2024-05-19 18:37 ` Re: Updating 457 rows in a table Rich Shepard <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Christophe Pettus @ 2024-05-19 18:32 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: [email protected]
> On May 19, 2024, at 11:30, Rich Shepard <[email protected]> wrote:
> 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.
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 ...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Updating 457 rows in a table
2024-05-19 16:56 Re: Updating 457 rows in a table Christophe Pettus <[email protected]>
2024-05-19 18:30 ` Re: Updating 457 rows in a table Rich Shepard <[email protected]>
2024-05-19 18:32 ` Re: Updating 457 rows in a table Christophe Pettus <[email protected]>
@ 2024-05-19 18:37 ` Rich Shepard <[email protected]>
2024-05-20 09:30 ` Re: Updating 457 rows in a table Alban Hertroys <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Rich Shepard @ 2024-05-19 18:37 UTC (permalink / raw)
To: [email protected]
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;
Thanks, Christophe,
Rich
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Updating 457 rows in a table
2024-05-19 16:56 Re: Updating 457 rows in a table Christophe Pettus <[email protected]>
2024-05-19 18:30 ` Re: Updating 457 rows in a table Rich Shepard <[email protected]>
2024-05-19 18:32 ` Re: Updating 457 rows in a table Christophe Pettus <[email protected]>
2024-05-19 18:37 ` Re: Updating 457 rows in a table Rich Shepard <[email protected]>
@ 2024-05-20 09:30 ` Alban Hertroys <[email protected]>
2024-05-20 12:33 ` Re: Updating 457 rows in a table Rich Shepard <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Alban Hertroys @ 2024-05-20 09:30 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general <[email protected]>
> On 19 May 2024, at 20:37, Rich Shepard <[email protected]> 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 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Updating 457 rows in a table
2024-05-19 16:56 Re: Updating 457 rows in a table Christophe Pettus <[email protected]>
2024-05-19 18:30 ` Re: Updating 457 rows in a table Rich Shepard <[email protected]>
2024-05-19 18:32 ` Re: Updating 457 rows in a table Christophe Pettus <[email protected]>
2024-05-19 18:37 ` Re: Updating 457 rows in a table Rich Shepard <[email protected]>
2024-05-20 09:30 ` Re: Updating 457 rows in a table Alban Hertroys <[email protected]>
@ 2024-05-20 12:33 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Rich Shepard @ 2024-05-20 12:33 UTC (permalink / raw)
To: pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-05-20 12:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-19 16:56 Re: Updating 457 rows in a table Christophe Pettus <[email protected]>
2024-05-19 18:30 ` Rich Shepard <[email protected]>
2024-05-19 18:32 ` Christophe Pettus <[email protected]>
2024-05-19 18:37 ` Rich Shepard <[email protected]>
2024-05-20 09:30 ` Alban Hertroys <[email protected]>
2024-05-20 12:33 ` Rich Shepard <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox