Horton
Menu

Thread

  1. UPDATE with multiple WHERE conditions

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-12T21:28:32Z

    I have a table with 3492 rows. I want to update a boolean column from
'false' to 'true' for 295 rows based on the value of another column.

Is there a way to access a file with those condition values? If not, should
I create a temporary table with one column containing those values, or do I
write a psql script with 295 lines, one for each row to be updated?

TIA,

Rich

  2. Re: UPDATE with multiple WHERE conditions

    Muhammad Salahuddin Manzoor <salahuddin.m@bitnine.net> — 2024-06-12T21:31:43Z

    Greetings,

You can use Temporary table. You could create a temporary table with one
column containing the condition values and then use it to update your main
table. This approach can be more flexible and cleaner than writing a script
with multiple update statements.

-- Create a temporary table with one column containing the condition values
CREATE TEMPORARY TABLE temp_conditions (condition_value TEXT);

-- Insert the condition values into the temporary table
INSERT INTO temp_conditions (condition_value) VALUES
    ('value1'),
    ('value2'),
    ('value3'),
    -- Add more values as needed...
    ('value295');

-- Update the boolean column based on the condition values
UPDATE your_table
SET boolean_column = true
WHERE condition_column IN (SELECT condition_value FROM temp_conditions);

-- Clean up: drop the temporary table
DROP TABLE IF EXISTS temp_conditions;

*Salahuddin (살라후딘**)*


On Thu, 13 Jun 2024 at 02:28, Rich Shepard <rshepard@appl-ecosys.com> wrote:

> I have a table with 3492 rows. I want to update a boolean column from
> 'false' to 'true' for 295 rows based on the value of another column.
>
> Is there a way to access a file with those condition values? If not, should
> I create a temporary table with one column containing those values, or do I
> write a psql script with 295 lines, one for each row to be updated?
>
> TIA,
>
> Rich
>
>
>

  3. Re: UPDATE with multiple WHERE conditions

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-12T21:34:09Z

    On Thu, 13 Jun 2024, Muhammad Salahuddin Manzoor wrote:

> You can use Temporary table. You could create a temporary table with one
> column containing the condition values and then use it to update your main
> table. This approach can be more flexible and cleaner than writing a
> script with multiple update statements.

Salahuddin,

Thank you. I thought this would be the best approach.

Regards,

Rich

  4. Re: UPDATE with multiple WHERE conditions

    Ron Johnson <ronljohnsonjr@gmail.com> — 2024-06-12T21:48:26Z

    On Wed, Jun 12, 2024 at 5:28 PM Rich Shepard <rshepard@appl-ecosys.com>
wrote:

> I have a table with 3492 rows. I want to update a boolean column from
> 'false' to 'true' for 295 rows based on the value of another column.
>
> Is there a way to access a file with those condition values? If not, should
> I create a temporary table with one column containing those values, or do I
> write a psql script with 295 lines, one for each row to be updated?
>

A plain UPDATE might work.

UPDATE to_be_updated a
SET bool_col = true
FROM other_table b
WHERE a.pk = b.pk
  AND b.field3 = mumble;

(You can join them, right?)

  5. Re: UPDATE with multiple WHERE conditions

    Rob Sargent <robjsargent@gmail.com> — 2024-06-12T22:05:26Z

    
On 6/12/24 15:48, Ron Johnson wrote:
> On Wed, Jun 12, 2024 at 5:28 PM Rich Shepard 
> <rshepard@appl-ecosys.com> wrote:
>
>     I have a table with 3492 rows. I want to update a boolean column from
>     'false' to 'true' for 295 rows based on the value of another column.
>
>     Is there a way to access a file with those condition values? If
>     not, should
>     I create a temporary table with one column containing those
>     values, or do I
>     write a psql script with 295 lines, one for each row to be updated?
>
>
> A plain UPDATE might work.
> UPDATE to_be_updated a
> SET bool_col = true
> FROM other_table b
> WHERE a.pk <http://a.pk> = b.pk <http://b.pk>
>   AND b.field3 = mumble;
>
> (You can join them, right?)
Add "begin;" to that and try it.  If you don't get exactly UPDATE 295 
reported, then "rollback;";

  6. Re: UPDATE with multiple WHERE conditions

    David G. Johnston <david.g.johnston@gmail.com> — 2024-06-12T22:09:57Z

    On Wed, Jun 12, 2024 at 2:28 PM Rich Shepard <rshepard@appl-ecosys.com>
wrote:

> I have a table with 3492 rows. I want to update a boolean column from
> 'false' to 'true' for 295 rows based on the value of another column.
>
> Is there a way to access a file with those condition values?
>

I'll often just use a spreadsheet to build the 295 update commands and
copy-paste them into psql or whatnot.

David J.

  7. Re: UPDATE with multiple WHERE conditions

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-12T22:41:14Z

    On Wed, 12 Jun 2024, David G. Johnston wrote:

> I'll often just use a spreadsheet to build the 295 update commands and
> copy-paste them into psql or whatnot.

David,

I'll create and use a temporary table.

Thanks,

Rich

  8. Re: UPDATE with multiple WHERE conditions

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-12T22:46:37Z

    On Wed, 12 Jun 2024, Ron Johnson wrote:

> A plain UPDATE might work.
>
> UPDATE to_be_updated a
> SET bool_col = true
> FROM other_table b
> WHERE a.pk = b.pk
>  AND b.field3 = mumble;
>
> (You can join them, right?)

Thanks, Ron.

Rich

  9. Re: UPDATE with multiple WHERE conditions

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-06-12T22:47:24Z

    On Wed, 12 Jun 2024, Rob Sargent wrote:

> Add "begin;" to that and try it.  If you don't get exactly UPDATE 295
> reported, then "rollback;";

Got it, thanks.

Rich

  10. Re: UPDATE with multiple WHERE conditions

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-06-13T10:34:25Z

    On 2024-Jun-12, David G. Johnston wrote:

> On Wed, Jun 12, 2024 at 2:28 PM Rich Shepard <rshepard@appl-ecosys.com>
> wrote:
> 
> > I have a table with 3492 rows. I want to update a boolean column from
> > 'false' to 'true' for 295 rows based on the value of another column.
> 
> I'll often just use a spreadsheet to build the 295 update commands and
> copy-paste them into psql or whatnot.

A closely related technique: if you have a query that generates the
UPDATE commands you need, you can run it under \gexec in psql, and
they'll be executed.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Al principio era UNIX, y UNIX habló y dijo: "Hello world\n".
No dijo "Hello New Jersey\n", ni "Hello USA\n".