sql - MySQL: Updating an item only if certain conditions are met -

does know how update row if 1 of it's column not equal same coloumn in row in table?

let me explain. consider table. stores bookings , admin can decide 1 approve.

booking |  time | approved    0    |  5:00 |   no    1    |  5:00 |   no    2    |  6:00 |   yes    3    |  6:00 |   no 

the booking requests can overlap, such case booking 0 , 1. admin can decide either approve 0, or 1.

but approved bookings cannot overlap. example, since booking 2 approved 6:00, booking 3 cannot approved. unless admin decides disapparove booking 2 first.

how write query check no collision happen? thinking along lines of:

 update requests  set approved = 'yes'  booking = 3  , not exists (     (select time      requests     booking = 3)     =      (select time     requests     booking != 3)  ); 

but doesn't work.

any appreciated.

as side note. actual problem little more complicated. table looks more

booking |  start time | end time |approved    0    |  5:00       |   6:00   | no    1    |  5:00       |   7:00   | no    2    |  6:00       |   8:00   | yes    3    |  6:00       |   9:00   | no 

and i'm trying prevent overlapping bookings. i'm sure if got syntax right simple version of problem, i'll able figure out complex version too.

you're not using not exists correctly. argument should single subquery, not expression comparing 2 subqueries. in case, condition in subquery it's same time, different id 1 you're updating, , other 1 approved.

update request r1 set approved = 'yes' r1.id = 3 , not exists (     select *     request r2     r2.time = r1.time , r2.id != r1.id , r2.approved = 'yes' ) 

to around error, use left join / null pattern.

update request r1 left join request r2 on r2.time = r1.time , r2.id != r1.id , r2.approved = 'yes' set r1.approved = 'yes' r1.id = 3 , r2.id null