A little help with join query

[mastubbs]

Hi guys,

 

I’m really hoping someone can help with this query. I'm sure it must use join somehow but i cant work out exactly how to do it.

 

I have two tables (‘booking_slots’ and ‘booking_reservation’). ‘booking_slots’ has ‘slot_date’ and ‘slot_id’ fields. ‘booking_reservation’ has a number of fields including ‘slot_id’ but not ‘slot_date’.

 

I want to run a query to delete all records between a certain date range in BOTH tables (say for example 01 Jan 2012 to 01 Jan 2013). To do this I want to find and delete all records using ‘slot_date’ in ‘booking_slots’ and use the corresponding ‘slot_id’ of the deleted records to delete the records with the same ‘slot_id’ in ‘booking_reservation’.

 

Any help with this would be very greatly appreciated.

 

Thanks

[Barand]

Not tested. Backup data before trying.

 

Assumes the dates are stored correctly in yyyy-mm-dd format (other formats will not work for range comparisons)

DELETE booking_slots, booking_reservation
FROM booking_slots
    INNER JOIN booking_reservation USING (slot_id)
WHERE booking_slots.slot_date BETWEEN '2012-01-01' AND '2013-01-01'

[mastubbs]

 

Not tested. Backup data before trying.

 

Assumes the dates are stored correctly in yyyy-mm-dd format (other formats will not work for range comparisons)

DELETE booking_slots, booking_reservation
FROM booking_slots
    INNER JOIN booking_reservation USING (slot_id)
WHERE booking_slots.slot_date BETWEEN '2012-01-01' AND '2013-01-01'

 

 

Thanks very much for the help. Unfortunately it didn't work and i cant really figure out why. The dates are in the correct format but it only deleted 6 rows but there are thousands that should match. The only thing i can think to add is that although slot_id is sequential in booking_slots, it is not in booking_reservation. Ie, in booking_slots, booking_id numbers are 1,2,3,4 etc but in booking_reservation there may not be a 'reservation' with booking_id=2 (for example).  In context, there are hundreds of booking slots for each day, but they are not always filled. Only filled (reserved) slots end up in booking_reservation with the slot_id number for that slot - if that makes sense? I'm not sure if that would cause a problem with the query though?

Edited December 18, 2014 by mastubbs

[Barand]

Try switching it to a select query to see which rows it thinks should be deleted and see if that gives any clues

SELECT *
FROM booking_slots
INNER JOIN booking_reservation USING (slot_id)
WHERE booking_slots.slot_date BETWEEN '2012-01-01' AND '2013-01-01'