Does anyone know how to remove entries where column 'BC' equals B1 and where column 'NI' matches i.e. in the below output B1 exists and column 'NI' matches for 3 entries so they should all be removed.

select mbd.mf, mml.ef, ni, mcb.bc
from m_mc_lg mml
inner join mem_b_det mbd
on mml.mf=mbd.mf
and mml.cf=mbd.cf
inner join mem_e me
on me.mf=mml.mf
and me.cf=mml.cf 
and me.ef=mml.ef
join mem_care_ben mcb
on mcb.mf=mbd.mf
and mcb.cf=mbd.cf 
and mcb.ef=mml.ef

CodePudding user response：

Try Some thing like this,

DELETE FROM m_mc_lg 
WHERE ni IN (SELECT ni FROM m_mc_lg WHERE BC = 'B1')

CodePudding user response：

I think you should try this query:

DELETE FROM m_mc_lg 
WHERE BC='B1' 
       OR 
      NI LIKE '