Deleting records based on Item matching and Drill Level

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  March 30, 2017 at 12:00 pm

  #150571

  Would like to write query which will delete all the reocrds at that TreeLevel where the item and matchItem is not same.

  In below example query should only show or delete rows 3 to 8 as At Row 2 the Item does not match MatchItem.

  CREATE

  TABLE drilldown (

  Row# Int,

  Level Int,

  Item varchar(100),

  MatchItem varchar(100)

  )

  
  

  insert into drilldown values( 1, 0, 'MainItem','MainItem')

  insert into drilldown values( 2, 1, 'SubItem1','SubItem200')

  insert into drilldown values( 3, 2, 'Sub-SobItem2','Sub-SobItem2')

  insert into drilldown values( 4, 3, 'Sub-SobItem3','Sub-SobItem3')

  insert into drilldown values( 5, 3, 'Sub-SobItem4','Sub-SobItem4')

  insert into drilldown values( 6, 2, 'Sub-SobItem5','Sub-SobItem5')

  insert into drilldown values( 7, 3, 'Sub-SobItem6','Sub-SobItem6')

  insert into drilldown values( 8, 3, 'Sub-SobItem7','Sub-SobItem7')

  insert into drilldown values( 9, 1, 'SubItem2','SubItem2')

  insert into drilldown values( 10, 2, 'Sub-SobItem2','Sub-SobItem2')

  insert into drilldown values( 11, 2, 'Sub-SobItem3','Sub-SobItem3')

  insert into drilldown values( 12, 2, 'Sub-SobItem4','Sub-SobItem4')

  insert into drilldown values( 13, 2, 'Sub-SobItem5','Sub-SobItem5')

  insert into drilldown values( 14, 1, 'SubItem3','SubItem3')

  insert into drilldown values( 15, 2, 'sdada','sdada')

  insert into drilldown values( 16, 2, 'asdas','asdas')

  insert into drilldown values( 17, 2, 'asdas','asdas')

  insert into drilldown values( 18, 1, 'SubItem3','SubItem3')

  insert into drilldown values( 19, 2, 'sadasd','sadasd')

  insert into drilldown values( 20, 2, 'sadasd','sadasd')

  insert into drilldown values( 21, 2, 'sadasd','sadasd')

  insert into drilldown values( 22, 2, 'sadsad','sadsad')

  insert into drilldown values( 23, 1, 'SubItem4','SubItem4')

  insert into drilldown values( 24, 2, 'sadsad','sadsad')

  insert into drilldown values( 25, 2, 'sadad','sadad')

  insert into drilldown values( 26, 2, 'asdas','asdas')

  insert into drilldown values( 27, 1, 'SubItem5','SubItem5')

  insert into drilldown values( 28, 2, 'sadsad','sadsad')

  insert into drilldown values( 29, 2, 'asdasd','asdasd')

  insert into drilldown values( 30, 2, 'sadsad','sadsad')

  insert into drilldown values( 31, 1, 'SubItem6','SubItem6')

  insert into drilldown values( 32, 1, 'SubItem7','SubItem7')

  insert into drilldown values( 33, 1, 'SubItem8','SubItem8')

  insert into drilldown values( 34, 1, 'SubItem9','SubItem9')

  insert into drilldown values( 35, 1, 'SubItem10','SubItem10')

  insert into drilldown values( 36, 1, 'SubItem11','SubItem11')

• bonggaspar

  Valued Member

  Points: 74

  More actions

  July 23, 2017 at 11:38 am

  #1952270

  Hi skb 44459,

  Can you please more specific on the requirement? Can you please explain more about the TreeLevel since your example says that it will only delete rows from 3 to 8 only? Thank you.