EXPERT RESPONSE
You need to investigate and fix
these anomalies, perhaps by deleting the child rows,
or adding the parent rows that are missing.
Here's the query to find them:
select TPR_PCSG_CO_ID
, MXFG_ID
, MCSMS_TRNS_HDR_ID
, MCSMS_MVEI_ID
from EDI.HM_MCSMD as D
where not exists
( select 1
from EDI.HM_MCSMS
where TPR_PCSG_CO_ID
= D.TPR_PCSG_CO_ID
and MXFG_ID
= D.MXFG_ID
and MCSMS_TRNS_HDR_ID
= D.MCSMS_TRNS_HDR_ID
and MCSMS_MVEI_ID
= D.MCSMS_MVEI_ID )
There are a number of ways to write
a query to find missing related rows, but I like NOT EXISTS
with a correlated subquery because it says
what you want. Well, except for the SELECT 1 bit,
but that's another story (see
How does WHERE EXISTS ( SELECT NULL... ) work? 22 February 2002).
If your decision to fix the anomalies is to
delete the orphans, just change the query above to say
DELETE FROM instead of SELECT ... FROM.
On the other hand, if you will be creating parent rows
for the orphans to relate to, feed the query above
into an INSERT statement:
insert
into EDI.HM_MCSMS
( TPR_PCSG_CO_ID
, MXFG_ID
, MCSMS_TRNS_HDR_ID
, MCSMS_MVEI_ID )
select ...
The insert will only work if all the other columns
in the parent table, i.e. the non-key columns not mentioned
in the INSERT column list, have default values or are allowed to
go null.
For More Information
|
