MySQL Delete rows from table dependent on over -

i trying delete rows map_points table supply place_id, delete if place_id not ancestor of place_id entered same map_id

create table map_points {     map_id int,     place_id int }  create table place_relation {     ancestor int,     child int }   | map_id | place_id | +--------+----------+ |   10   |    20    | |   10   |    22    | |   12   |    20    | |   12   |    31    | |   12   |    21    | |   13   |    20    | |   13   |    44    | |   14   |    33    | |   14   |    31    | |   14   |    20    | |   14   |    44    | +--------+----------+   | ancestor | child | +----------+-------+ |   20     |   22  | |   20     |   21  | |   31     |   33  | +----------+-------+ 

i want delete map_points have place_id = 20, not if place has child in place_relation table. after executing delete result set should follows.

| map_id | place_id | +--------+----------+ |   10   |    20    | |   10   |    22    | |   12   |    20    | |   12   |    31    |<- deleted |   12   |    21    | |   13   |    20    |<- deleted |   13   |    44    | |   14   |    33    | |   14   |    31    | |   14   |    20    |<- deleted |   14   |    44    | +--------+----------+ 

here i'm at, deleting many records

delete p map_points p     join place_relation r on r.ancestor = p.place_id     left join map_points p2 on p2.map_id = p.map_id , p2.place_id = r.child p.place_id in ( 20, 31 ) , p2.place_id null 

edit

i have removed 1 of criteria left join, nothing deleted

delete p map_points p     join place_relation r on r.ancestor = p.place_id     left join map_points p2 on p2.place_id = r.child p.place_id in ( 20, 31 ) , p2.place_id null 

edit

based on original criteria following query gives correct results

delete p map_points p     join place_relation r on r.ancestor = p.place_id     left join map_points p2 on p2.map_id = p.map_id , p2.place_id in (         select child         place_relation          r.ancestor = ancestor      ) p.place_id in ( 20, 31 ) , p2.place_id null 

updated

while working query updated place_relation table adding row more accurately reflects data in our active database.

| ancestor | child | +----------+-------+ |    20    |   22  | |    20    |   21  | |    21    |   22  |* |    31    |   33  | +----------+-------+ 

desired end result

| map_id | place_id | +--------+----------+ |   10   |    20    | |   10   |    21    |* // ancestors added map_points table |   10   |    22    | |   12   |    20    |<- deleted |   12   |    31    |<- deleted |   12   |    21    |<- deleted |   13   |    20    |<- deleted |   13   |    44    | |   14   |    33    | |   14   |    31    | |   14   |    20    |<- deleted |   14   |    44    | +--------+----------+ 

then added 21 criteria ... p.place_id in (20,21,31)

the resulting delete did not remove row (12,20) map_points table.

so, looks query can take 1 value in criteria. thus, data need sorted before delete statement executed 1 value @ time.

is there way write query multiple values can entered in criteria?

updated

to desired result multiple values seems have enter multiple values in subselect

delete p map_points p     join place_relation r on r.ancestor = p.place_id     left join map_points p2 on p2.map_id = p.map_id , p2.place_id in (         select child         place_relation          r.ancestor = ancestor          , child not in (20,21,31)     ) p.place_id in (20,21,31) , p2.place_id null 

you have many conditions in left join:

delete p map_points p join      place_relation r      on r.ancestor = p.place_id left join      map_points p2      on p2.place_id = r.child p.place_id in ( 20, 31 ) , p2.place_id null; 

basically, conditions restrictive, no records in p2 match. hence, final condition, p2.place_id = null true -- , many records deleted.