Syntax error possibly due to multiple ANDs?

UPDATE bookings b
SET reservation_processed = 'archived'
  JOIN reservation_time_data r ON r.id = b.reservation_time
WHERE premises_code = 'LJJIDHhRN2ho1e3h'
 AND reservation_date LIKE '%2020-09-10%'
 AND CURRENT_TIME > ADDTIME(r.end_time, '0:30:0.000000')

I first got the error in php, and I then tested it in phpmyadmin and I get an error. It doesn’t specify why there is an error it just saying an error with the syntax. I thought it maybe because there is multiple AND clauses but I put them in brackets to maybe simplify it but it didn’t help 🙁 Anyone any ideas?

Answer

The correct syntax is:

UPDATE bookings b JOIN
       reservation_time_data r 
       ON r.id = b.reservation_time
    SET reservation_processed = 'archived'
WHERE premises_code = 'LJJIDHhRN2ho1e3h' AND
      reservation_date LIKE '%2020-09-10%' AND
      CURRENT_TIME > ADDTIME(r.end_time, '0:30:0.000000')

That said, your query should qualify all column references. You can still get an invalid time. Also, using LIKE on a date/column is wrong. So is storing a date as a string. If I assume that the value is really a date and speculate on where columns come from, then the query should look something like this:

UPDATE bookings b JOIN
       reservation_time_data r 
       ON r.id = b.reservation_time
    SET b.reservation_processed = 'archived'
WHERE b.premises_code = 'LJJIDHhRN2ho1e3h' AND
      r.reservation_date = '2020-09-10' AND
      CURRENT_TIME > ADDTIME(r.end_time, '0:30:0.000000')