Re: UPDATE command with FROM clause

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