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