CODEWITHSUNDEEP
mysql
lakeIn231
lakeIn231

Reputation: 1295

Trying to update a column in a different table with mysql

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

Upvotes: 6

Views: 134

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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

Upvotes: 2

Tarun Lalwani
Tarun Lalwani

Reputation: 146510

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

Upvotes: 4

Related Questions