Обсуждение: Updating 457 rows in a table

Searching the postgresql doc for UPDATE the examples I find show updating
one or a few rows in a table. I have 457 rows to update in a table.

I could write a .sql script with 457 lines, each updating one row of the
table. My web search for `sql: update table rows from a file of column
values' finds pages for single row updates and updating a table from another
table, but neither is what I want.

I want to change a column value in a table based on the value of a different
column in that same table.

Specifically, in the 'people' table I want to change the column 'active'
from false to true for 457 specific person_id row numbers.

Is there a way to do this without manually writing 457 'update ...' rows in
a .sql file?

TIA,

Rich

> On May 19, 2024, at 09:54, Rich Shepard <rshepard@appl-ecosys.com> 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
appropriateids. 

On 19/05/2024 17:54, Rich Shepard wrote:
> Searching the postgresql doc for UPDATE the examples I find show updating
> one or a few rows in a table. I have 457 rows to update in a table.
>
> I could write a .sql script with 457 lines, each updating one row of the
> table. My web search for `sql: update table rows from a file of column
> values' finds pages for single row updates and updating a table from 
> another
> table, but neither is what I want.
>
> I want to change a column value in a table based on the value of a 
> different
> column in that same table.
>
> Specifically, in the 'people' table I want to change the column 'active'
> from false to true for 457 specific person_id row numbers.
>
> Is there a way to do this without manually writing 457 'update ...' 
> rows in
> a .sql file?

Could you create a table with just person_id values whose rows are to be 
updated? Then you could do something like this:

update people set active = true where exists (
   select 1 from temporary_table where person_id = people.person_id
);


That's just off the top of my head and might not be correct, but that's 
the way I'd be thinking.

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

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

On Sun, 19 May 2024, Ray O'Donnell wrote:

> Could you create a table with just person_id values whose rows are to be 
> updated? Then you could do something like this:
>
> update people set active = true where exists (
>   select 1 from temporary_table where person_id = people.person_id
> );
>
> That's just off the top of my head and might not be correct, but that's the 
> way I'd be thinking.

Ray,

I thought of doing this but it's a one-off activity. I would create and
insert the table, then delete it when done. Will keep this in mind.

Thanks,

Rich

> On May 19, 2024, at 11:30, Rich Shepard <rshepard@appl-ecosys.com> 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 ...

On Sun, 19 May 2024, Muhammad Salahuddin Manzoor wrote:

> I think triggers are a good option.

Salahuddin,

I need to update the table with all the designated rows only once. But, I'll
look at using triggers.

Thanks,

Rich

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

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

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