CODEWITHSUNDEEP
mysql
JED
JED

Reputation: 1694

MySQL: Update based on two columns of inner join

I have table_a and a_audit. table_a has columns id, client_id, originating_client_id. a_audit has the same columns; it is used to track the changes to table_a.

I have this select query to get all rows from table_a where client_id IS NULL, originating_client_id IS NULL and there is a row in the audit table that contains a client_id:

select t.id,
       t.client_id,
       t.originating_client_id,
       a.client_id
from table_a t
inner join a_audit a
  on a.id = t.id
where t.client_id is null
  and t.originating_client_id is null
  and a.client_id is not null
group by ur.user_role_id /* not necessary? */

select query

What I want to do is: for each row in table_a where client_id IS NULL and originating_client_id IS NULL, find the first row in a_audit that has the same id and has a client_id, then set table_a.originating_client_id = client_id from that row of a_audit

intent

Upvotes: 0

Views: 31

Answers (1)

forpas
forpas

Reputation: 164089

Get all the rows of a_audit with the minimum updated_on for each id and client_id not null and join to table_a:

update table_a t inner join (
  select a.* from  a_audit a
  where a.client_id is not null
  and not exists (
    select 1 from a_audit
    where id = a.id and updated_on < a.updated_on
  )  
) a on a.id = t.id
set t.originating_client_id = a.client_id
where t.client_id is null and t.originating_client_id is null

Upvotes: 1

Related Questions