Re: UPDATE command with FROM clause

On Tue, Aug 13, 2019 at 11:37 AM JORGE MALDONADO <[email protected]>
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.