Trying to update a column in a different table with mysql

I would use WITH statement in this to make things look cleaner

WITH 
source_data as (
SELECT
     RIGHT(tagarr, LENGTH(tagarr) - LENGTH('encoder:')) enc, mo.src_ip, mo.datacenter
FROM
    logical_service ls, mpeg_out mo, UNNEST(ls.tags) AS tagarr
WHERE
    tagarr LIKE 'encoder:%'
    AND mo.lid = ls.lid )

UPDATE encoder as en
    SET output_source_ip = (
    select src_ip from source_data as sd
    where sd.datacenter = en.datacenter and sd.encoder = en.encoder
)

That should do the job for you


One way is to utilize a Derived Table (subquery), to fetch the src_ip values, and then JOIN to the encoder table on datacenter and encoder fields; and update the values, if there is a match.

Also, please don't use Old comma based Implicit joins and use Modern Explicit Join based syntax. I have changed your SELECT query to use Explicit JOIN .. ON syntax.

UPDATE encoder AS e 
JOIN 
(
  SELECT
    RIGHT(tagarr, LENGTH(tagarr) - LENGTH('encoder:')) AS encoder, 
    mo.src_ip, 
    mo.datacenter
  FROM
    logical_service AS ls 
    JOIN mpeg_out AS mo ON mo.lid = ls.lid 
    JOIN UNNEST(ls.tags) AS tagarr ON tagarr LIKE 'encoder:%'
) AS dt ON dt.encoder = e.encoder 
           AND dt.datacenter = e.datacenter 
SET e.output_source_ip = dt.src_ip 
WHERE e.output_source_ip IS NULL 

Tags:

Mysql