Subject: Re: UPDATE command with FROM clause
To: pgsql-sql@lists.postgresql.org
References: 
 <CAAY=A7-UcNWsakeEALHfdZMQ_0qZaBFbY+JM+qF1_mnCX-ckaw@mail.gmail.com>
From: Rob Sargent <robjsargent@gmail.com>
Message-ID: <b9240eff-0e2b-b260-8177-94076984cddf@gmail.com>
Date: Tue, 13 Aug 2019 15:45:57 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CAAY=A7-UcNWsakeEALHfdZMQ_0qZaBFbY+JM+qF1_mnCX-ckaw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------359ACD02132022D4EC906064"
Content-Language: en-US
Precedence: bulk

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


On 8/13/19 12:37 PM, JORGE MALDONADO wrote:
> Hi,
>
> I have a query like this:
>
> UPDATE chartsclub.secc_esp_votar_votos
> SET svv_puntos = 0 FROM
> (SELECT * FROM chartsclub.secc_esp_votar_votos AS tblVotos WHERE 
> svv_sva_clave = 114 EXCEPT
>        (SELECT DISTINCT ON (svv_fechareg) * FROM 
> chartsclub.secc_esp_votar_votos AS tblVotos WHERE svv_sva_clave = 114 
> ORDER BY svv_fechareg))
>
> Will the UPDATE command affect only (all) records generated by the 
> SELECT clause in the FROM clause?
>
> I suppose that, if I include a WHERE clause, the condition will be 
> applied to the records obtained by the SELECT clause in the FROM clause.
> Is this correct?
>
> My goal is to get a set of records from one table and update only such 
> a set of records.
> In this case, the set of records to be updated are those obtained by 
> the SELECT command in the FROM clause.
> As you can see, there is only one table involved but I added an alias 
> to the SELECT statement in the FROM clause based on what I read in the 
> documentation.
>
> Best regards,
> Jorge Maldonado
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> 
> 	Libre de virus. www.avast.com 
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> 
>
>

I'm not clear how the value of svv_fechareg affects which rows you don't 
want to update but I think all you need is something along the lines of

    UPDATE chartsclub.secc_esp_votar_votos

    set svv_puntos = 0

    where svv_sva_clave =114

    --here's where the purpose of svv_fechareg comes into play. if you
    have an explicit value(s) for it, apply that as "!=" or "not in
    (value, value)"

    and svv_fechareg is not null


--------------359ACD02132022D4EC906064
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 8/13/19 12:37 PM, JORGE MALDONADO
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAAY=A7-UcNWsakeEALHfdZMQ_0qZaBFbY+JM+qF1_mnCX-ckaw@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hi,
        <div><br>
        </div>
        <div>I have a query like this:</div>
        <div><br>
        </div>
        <div>UPDATE chartsclub.secc_esp_votar_votos <br>
          SET svv_puntos = 0 FROM<br>
          (SELECT * FROM chartsclub.secc_esp_votar_votos AS tblVotos
          WHERE svv_sva_clave = 114 EXCEPT <br>
                 (SELECT DISTINCT ON (svv_fechareg) * FROM
          chartsclub.secc_esp_votar_votos AS tblVotos WHERE
          svv_sva_clave = 114 ORDER BY svv_fechareg))<br>
        </div>
        <div><br>
        </div>
        <div>Will the UPDATE command affect only (all) records generated
          by the SELECT clause in the FROM clause?</div>
        <div><br>
        </div>
        <div>I suppose that, if I include a WHERE clause, the condition
          will be applied to the records obtained by the SELECT clause
          in the FROM clause. </div>
        <div>Is this correct?</div>
        <div><br>
        </div>
        <div>My goal is to get a set of records from one table and
          update only such a set of records.</div>
        <div>In this case, the set of records to be updated are those
          obtained by the SELECT command in the FROM clause.</div>
        <div>As you can see, there is only one table involved but I
          added an alias to the SELECT statement in the FROM clause
          based on what I read in the documentation.</div>
        <div><br>
        </div>
        <div>Best regards,</div>
        <div>Jorge Maldonado</div>
      </div>
      <div id="DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br>
        <table style="border-top:1px solid #d3d4de">
          <tbody>
            <tr>
              <td style="width:55px;padding-top:18px"><a
href="https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail"
                  target="_blank" moz-do-not-send="true"><img
src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif"
                    alt="" style="width: 46px; height: 29px;"
                    moz-do-not-send="true" width="46" height="29"></a></td>
              <td
style="width:470px;padding-top:17px;color:#41424e;font-size:13px;font-family:Arial,Helvetica,sans-serif;line-height:18px">Libre
                de virus. <a
href="https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=webmail"
                  target="_blank" style="color:#4453ea"
                  moz-do-not-send="true">www.avast.com</a></td>
            </tr>
          </tbody>
        </table>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p>I'm not clear how the value of svv_fechareg affects which rows
      you don't want to update but I think all you need is something
      along the lines of <br>
    </p>
    <blockquote>
      <p>UPDATE chartsclub.secc_esp_votar_votos</p>
      <p>set svv_puntos = 0</p>
      <p>where svv_sva_clave =114 <br>
      </p>
      <p>--here's where the purpose of svv_fechareg comes into play. if
        you have an explicit value(s) for it, apply that as "!=" or "not
        in (value, value)"<br>
      </p>
      <p>and svv_fechareg is not null </p>
    </blockquote>
    <p><br>
    </p>
  </body>
</html>

--------------359ACD02132022D4EC906064--