UPDATE command with FROM clause

Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

UPDATE command with FROM clause

JORGE MALDONADO
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

Libre de virus. www.avast.com
lup
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE command with FROM clause

lup


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

Libre de virus. www.avast.com


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 


Reply | Threaded
Open this post in threaded view
|

Re: UPDATE command with FROM clause

David G Johnston
In reply to this post by JORGE MALDONADO
On Tue, Aug 13, 2019 at 11:37 AM JORGE MALDONADO <[hidden email]> 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.