Synia Synia - 1 year ago 49
MySQL Question

MySQL: Updating an item only if certain conditions are met

Does anyone know how to update a row only if one of it's column does not equal to the same coloumn in another row in the table?

Let me explain. Consider this table. It stores some bookings and the admin can decide which one to 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 is the case with Booking 0 and 1. The admin can decide either to approve 0, or 1.

But approved bookings cannot overlap. For example, Since booking 2 is already approved for 6:00, booking 3 cannot be approved. Unless the admin decides to disapparove booking 2 first.

How would I write a query for this to check that no collision will happen? I was thinking something along the lines of:

UPDATE requests
SET Approved = 'YES'
WHERE Booking = 3
FROM requests
WHERE Booking = 3)
From requests
WHERE Booking != 3)

But that doesn't work.

Any help would be much appreciated.

As a side note. My actual problem is a little more complicated. The table looks more like

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 to prevent overlapping bookings. But I'm sure if I got the syntax right for the simple version of the problem, then i'll be able to figure out the complex version too.

Answer Source

You're not using NOT EXISTS correctly. Its argument should be a single subquery, not an expression comparing two subqueries. In your case, the condition in the subquery is that it's the same time, but a different ID than the one you're updating, and the other one is already approved.

UPDATE request AS r1
SET approved = 'YES'
    SELECT *
    FROM request AS r2
    WHERE r2.time = r1.time AND != AND r2.approved = 'YES'