How do I perform an UPDATE with a SELECT of the field and the rows I need to Update? [closed]

Question

How do I perform an UPDATE with a SELECT of the field and the rows I need to Update? [closed]

Navigation

#1 by (1 votes)

-2

I need to update 21 rows of a certain field, but to get to these 21 rows I have to make select e inner join with another table, as you can do UPDATE to this 21 rows.

This is the SELECT that I use to get the data.

SELECT clave_cpi
  FROM catcpi_alumnos A
 INNER JOIN catcpi ID
    ON A.idcpi = ID.idcpi
 WHERE TRIM(gpogeneracional) = '16MSP-DURANGO'

postgresql

asked by Pablo Perez 06.01.2017 в 20:37

source

1 answer

Close modal window in CSS [closed] assign an icon to a qfile

score 1 · Accepted Answer

You can try to make a subquery

UPDATE dummy
SET customer=subquery.customer,
    address=subquery.address,
    partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
      FROM  /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;

Something like that

UPDATE catcpi
SET catcpi.clave_cpi=subquery.clave_cpi
FROM (
    SELECT clave_cpi , A.idcpi
    FROM catcpi_alumnos A 
    INNER JOIN catcpi ID ON A.idcpi = ID.idcpi 
    WHERE TRIM(gpogeneracional) = '16MSP-DURANGO') AS subquery
WHERE catcpi.idcpi=subquery.idcpi;