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
Re: Updating 457 rows in a table
> On May 19, 2024, at 09:54, Rich Shepard 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.
Re: Updating 457 rows in a table
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 r...@rodonnell.ie
Re: Updating 457 rows in a table
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 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
Re: Updating 457 rows in a table
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
Re: Updating 457 rows in a table
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
Re: Updating 457 rows in a table
> On May 19, 2024, at 11:30, Rich Shepard 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 ...
Re: Updating 457 rows in a table
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
Re: Updating 457 rows in a table
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
