CODEWITHSUNDEEP
mysql
Tom Melly
Tom Melly

Reputation: 108

Insert into master table when detail records present but missing master

I have two tables - one master, one detail (i.e. a one-to-many pair of tables). I'm importing data from a horrible schema and one feature of the data is that often I have some detail records but no master.

How would go about inserting master records in these cases? I can locate the missing masters easily enough with this query:

select * from p_ltx_surgical_comp as c -- detail
left join p_ltx_surgical as s -- master
on c.fk_oid = s.fk_oid -- this is the key
where s.oid is null -- primary key, so null means no record exists
group by c.fk_oid; -- only show one value even if there are multiple detail records

Oh, and as an extra wrinkle, I only want to insert a single master even if there a are multiple detail records.

Upvotes: 1

Views: 104

Answers (2)

Tom Melly
Tom Melly

Reputation: 108

Ah, I was so close... this seems to have worked:

insert into p_ltx_surgical (oid, fk_oid, ltx_surg_date)
select sp_getvdtablekey('p_ltx_surgical', 0), c.fk_oid, '1900-01-01' from
p_ltx_surgical_comp as c -- detail
left join p_ltx_surgical as s -- master
on c.fk_oid = s.fk_oid -- this is the key
where s.oid is null
group by c.fk_oid; -- primary key, so null means no record exists

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You can start with this INSERT query:

INSERT INTO p_ltx_surgical (fk_oid)
SELECT DISTINCT c.fk_oid
FROM
  p_ltx_surgical_comp AS c
  LEFT JOIN p_ltx_surgical AS s
  ON c.fk_oid = s.fk_oid
WHERE
  s.oid IS NULL

and you can add more details to your table, for example:

INSERT INTO p_ltx_surgical (fk_oid, description, ...)
SELECT DISTINCT c.fk_oid, 'missing record', ...
FROM
   ...

Upvotes: 2

Related Questions