Trying to update a column in a different table with mysql

Question

I am grabbing 1000 records of data with this query:

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

That creates 3 columns of data that looks like this:

encoder | src_ip | datacenter

I then have an encoder table that has fields

encoder | output_source_ip | datacenter

the output_source_ip is primarily null, so I want to update that column on the encoder table with src_ip if the select on encoder and datacenter matches with encoder and datacenter.

Any idea how I can do this? Here is my attempt at doing it, but it is definitely broken:

UPDATE encoder 
    SET output_source_ip = (
SELECT
     RIGHT(tagarr, LENGTH(tagarr) - LENGTH('encoder:')) encoder, 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
);

Answer 1

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