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
);

Please show an example of the data and the desired result for that data. — Rick James, Jun 30 '19 at 21:52

score 4 · Accepted Answer · answered Jun 30 '19 at 18:44

4

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

answered Jun 30 '19 at 18:44

Tarun Lalwani

142,312
9
204
265

The use of `WITH` requires MySQL 8.0. @Jay266, what version are you using? – Rick James Jun 30 '19 at 21:46
Since, OP has accepted this as an answer, he is using MySQL 8.0+ – James Jul 07 '19 at 07:57

score 2 · Answer 2 · answered Jul 01 '19 at 07:40

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

Trying to update a column in a different table with mysql

2 Answers2