Re: Updating 457 rows in a table
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
Re: Updating 457 rows in a table
> On 19 May 2024, at 20:37, Rich Shepard 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.
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
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 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, 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 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
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 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
> 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.