Content-Type: multipart/alternative;
 boundary="------------B4NHV0EmI8zknL96jFzTp1eC"
Message-ID: <2587edf4-cd3e-46bd-8203-2881fc3902e9@gmail.com>
Date: Wed, 12 Jun 2024 16:05:26 -0600
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: UPDATE with multiple WHERE conditions
To: pgsql-general@lists.postgresql.org
References: <c9cf4869-9d6d-7165-3f9f-611446bf961@appl-ecosys.com>
 <CANzqJaB48SjPZrOqEn-wawob+GfKQrwLouyNP_zoPW_5QsvFFw@mail.gmail.com>
Content-Language: en-CA
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: <CANzqJaB48SjPZrOqEn-wawob+GfKQrwLouyNP_zoPW_5QsvFFw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/2587edf4-cd3e-46bd-8203-2881fc3902e9%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------B4NHV0EmI8zknL96jFzTp1eC
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


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;";

--------------B4NHV0EmI8zknL96jFzTp1eC
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <br>
    <br>
    <div class="moz-cite-prefix">On 6/12/24 15:48, Ron Johnson wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CANzqJaB48SjPZrOqEn-wawob+GfKQrwLouyNP_zoPW_5QsvFFw@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">On Wed, Jun 12, 2024 at 5:28 PM Rich Shepard &lt;<a
            href="mailto:rshepard@appl-ecosys.com"
            moz-do-not-send="true" class="moz-txt-link-freetext">rshepard@appl-ecosys.com</a>&gt;
          wrote:<br>
        </div>
        <div class="gmail_quote">
          <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">I
            have a table with 3492 rows. I want to update a boolean
            column from<br>
            'false' to 'true' for 295 rows based on the value of another
            column.<br>
            <br>
            Is there a way to access a file with those condition values?
            If not, should<br>
            I create a temporary table with one column containing those
            values, or do I<br>
            write a psql script with 295 lines, one for each row to be
            updated?<br>
          </blockquote>
          <div><br>
          </div>
          <div>A plain UPDATE might work.  </div>
          <div> </div>
          <div><font face="monospace">UPDATE to_be_updated a</font></div>
          <div><font face="monospace">SET bool_col = true</font></div>
          <div><font face="monospace">FROM other_table b</font></div>
          <div><font face="monospace">WHERE <a href="http://a.pk"
                moz-do-not-send="true">a.pk</a> = <a href="http://b.pk"
                moz-do-not-send="true">b.pk</a></font></div>
          <div><font face="monospace">  AND b.field3 = mumble;</font></div>
          <div><br>
          </div>
          <div>(You can join them, right?)</div>
        </div>
      </div>
    </blockquote>
    Add "begin;" to that and try it.  If you don't get exactly UPDATE
    295 reported, then "rollback;";<br>
    <br>
  </body>
</html>

--------------B4NHV0EmI8zknL96jFzTp1eC--