agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedUPDATE command with FROM clause
3+ messages / 3 participants
[nested] [flat]
* UPDATE command with FROM clause
@ 2019-08-13 18:37 JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: JORGE MALDONADO @ 2019-08-13 18:37 UTC (permalink / raw)
To: pgsql-sql
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&...;
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE command with FROM clause
@ 2019-08-13 21:45 Rob Sargent <robjsargent@gmail.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Rob Sargent @ 2019-08-13 21:45 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE command with FROM clause
@ 2019-08-13 22:47 David G. Johnston <david.g.johnston@gmail.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2019-08-13 22:47 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
On Tue, Aug 13, 2019 at 11:37 AM JORGE MALDONADO <jorgemal1960@gmail.com>
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?
>
The FROM clause allows you to supply a second table that can be referenced
in the WHERE clause of the UPDATE. If the FROM entry is not referenced in
the WHERE clause (or there isn't one) then it might as well not exist as
every single record on the UPDATE table will be affected since you haven't
applied a WHERE clause to filter which ones.
FROM is always optional, you can always write a WHERE clause to directly
apply the same filter - usually by selecting the PK from the UPDATE table
and doing:
UPDATE tbl SET ... WHERE tbl.id IN (SELECT reftbl.id FROM tbl AS reftbl
JOIN/WHERE/WHATEVER)
self-joining in an UPDATE should probably be avoided (FROM in general
should probably be avoided, IMO)
e.g.,
UPDATE ...
SET ... = 0
WHERE svv_sva_clave = 114
AND id <> (SELECT id FROM ... WHERE svv_sva_clave = 114 ORDER BY
svv_fechareg LIMIT 1)
(are you sure you don't want to sort svv_fechareg descending - newest to
oldest?)
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-08-13 22:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-08-13 18:37 UPDATE command with FROM clause JORGE MALDONADO <jorgemal1960@gmail.com>
2019-08-13 21:45 ` Rob Sargent <robjsargent@gmail.com>
2019-08-13 22:47 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox