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