Search code examples
mysqlinsert-update

Update a Mysql table from Select

I have the following select:

select a.TX , b.PSP_map as PSP_map_DC from psp_DC_CO_consol a 
left join psp_DC_CO_consol b on b.TX=a.TX
where a.source='CORE' and a.PSP_map is NULL and a.PSP<>'' and a.TX<>'' and a.StatusTX=1 
and b.PSP_map is not NULL

i Need now to update the column PSP_map with the 'PSP_map_DC' column from my select in the table psp_DC_CO_consol and only for the rows with source='CORE' and TX= TX from my select.

I have tried with no success.

Solution

  • You need a self join in the UPDATE statement, like your query, but it should be an INNER join since you have the condition b.PSP_map IS NOT NULL:

    UPDATE psp_DC_CO_consol a
INNER JOIN psp_DC_CO_consol b ON b.TX = a.TX
SET a.PSP_map = b.PSP_map
WHERE a.source='CORE' AND a.PSP_map is NULL AND a.PSP <> '' 
  AND a.TX <> '' AND a.StatusTX = 1 AND b.PSP_map IS NOT NULL