sql - MySQl Query giving wrong result -

select * yogatimetable;  delete  yogatimetable roomnum in (select tt.roomnum                   yogarooms r,                         yogatypes t,                         yogatimetable tt                    r.roomnum = tt.roomnum , ((r.roomcapacity * t.classprice) - (r.costperhour * tt.duration / 60)) < 200);  select * yogatimetable; 

the goal delete classes timetable can make less $200 profit. calculate profitability of each class, multiply roomcapacity classprice , subtract cost of room. calculate cost of room multiply costperhour duration divided 60. isn't giving right result, can tell me made mistake. thanks. tables attached.

to me looks have 2 problems.

1. a cross join between t , tt exists , should resolved.
2. you're attempting delete based on incomplete or partial key of yogatimetable. unique key of yogatimetable appears yogaid, starttime,day , roomnum. because same yoga type in same room @ same time on different day, or in same room on same day @ different start times. think unique key yogatimetable composite key of 4 fields. when deleting need use complete key, not partial key.

so result in. .

delete yogatimetable exists  (select 1  yogarooms r  inner join yogatimetable tt     on r.roomnum = tt.roomnum   inner join yogatypes t    on tt.yogaid = t.yogaid   yogatimetable.yogaid = tt.yogaid    , yogatimetable.roomnum = tt.roomnum    , yogatimetable.starttime = tt.starttime    , yogatimetable.day = tt.day    , ((r.roomcapacity * t.classprice) - (r.costperhour * tt.duration / 60)) < 200);  

according to: can use correlated subquery delete can't alias table.... https://bugs.mysql.com/bug.php?id=2920