agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: UPDATE command with FROM clause
Date: Tue, 13 Aug 2019 15:45:57 -0600
Message-ID: <b9240eff-0e2b-b260-8177-94076984cddf@gmail.com> (raw)
In-Reply-To: <CAAY=A7-UcNWsakeEALHfdZMQ_0qZaBFbY+JM+qF1_mnCX-ckaw@mail.gmail.com>
References: <CAAY=A7-UcNWsakeEALHfdZMQ_0qZaBFbY+JM+qF1_mnCX-ckaw@mail.gmail.com>
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&...;
> Libre de virus. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
>
>
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
view thread (3+ messages) latest in thread
Message-ID: <b9240eff-0e2b-b260-8177-94076984cddf@gmail.com>
Permalink: ../b9240eff-0e2b-b260-8177-94076984cddf@gmail.com/
Also on: postgresql.org/message-id/b9240eff-0e2b-b260-8177-94076984cddf@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: UPDATE command with FROM clause
In-Reply-To: <b9240eff-0e2b-b260-8177-94076984cddf@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox