How to query this dataset to remove entries

lostcoder 0
Viewed 36

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
MF EF NI BC
8047002 1 AA123456A A1
7045684 1 BB123456B B1
7045684 1 BB123456B B2
7045684 1 BB123456B B3
6082495 1 CC123456C C1
plsql sql
3 Answers

Try Some thing like this,

DELETE FROM m_mc_lg 
WHERE ni IN (SELECT ni FROM m_mc_lg WHERE BC = 'B1')
Jaydip Jadhav0
Ehsan MrH0

If you want to only "remove" this entries from your query result, not from your table, you could try this:

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
WHERE mcb.bc <> 'B1'
AND NI !~ 'B1'

https://dbfiddle.uk/MSiDm1TF

radeklukasiewicz0
Related