Horton
Menu

Thread

  1. Updating 457 rows in a table

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-19T16:54:46Z

    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

  2. Re: Updating 457 rows in a table

    Christophe Pettus <xof@thebuild.com> — 2024-05-19T16:56:50Z

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

  3. Re: Updating 457 rows in a table

    Ray O'Donnell <ray@rodonnell.ie> — 2024-05-19T16:57:41Z

    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

  4. Re: Updating 457 rows in a table

    Muhammad Ikram <mmikram@gmail.com> — 2024-05-19T17:06:04Z

    Hi Rich,

Based on what I could understand is, here is an example

UPDATE employees
SET  salary = salary + 500
WHERE department_id = 'Sales';

Sorry, if I misunderstood your question.

Regards,
Muhammad Ikram
Bitnine

On Sun, May 19, 2024 at 9:54 PM Rich Shepard <rshepard@appl-ecosys.com>
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?
>
> TIA,
>
> Rich
>
>
>
>

-- 
Muhammad Ikram

  5. Re: Updating 457 rows in a table

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-19T18:30:30Z

    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

  6. Re: Updating 457 rows in a table

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-19T18:32:27Z

    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

  7. Re: Updating 457 rows in a table

    Christophe Pettus <xof@thebuild.com> — 2024-05-19T18:32:39Z

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

  8. Re: Updating 457 rows in a table

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-19T18:35:42Z

    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

  9. Re: Updating 457 rows in a table

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-19T18:37:50Z

    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

  10. Re: Updating 457 rows in a table

    Alban Hertroys <haramrae@gmail.com> — 2024-05-20T09:30:29Z

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

  11. Re: Updating 457 rows in a table

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-20T12:33:37Z

    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