Can't use MySQL extract() function in the WHERE clause.

Question

I've run the following query:

    UPDATE main_table, reference_table
  SET main_table.calc_column = 
    (CASE WHEN main_table.incr = "6AM" 
          THEN reference_table.col1+reference_table.col2+...
          WHEN main_table.incr = "12AM" 
          THEN reference_table.col7+reference_table.col8+...
          WHEN main_table.incr = "6PM" 
          THEN reference_table.col13+reference_table.col14+...
          ELSE reference_table.col19+reference_table.col20+...)
  WHERE main_table.month = extract(month from reference_table.thedate)
    AND main_table.day = extract(day from reference_table.thedate)

I've used extract() function since my reference_table doesn't have month and day columns but has the date column named thedate. I've used the extract() function on the reference_table many times before successfully, so, I know that there's nothing wrong with my extract function syntax. However, in this instance, MySQL complains. It probably has to do with the fact that I've used in the WHERE clause.

I know that this issue could get fixed if I added the month and day columns to the reference_table to avoid using the extract() function. However, I'm very reluctant to do that and would like to avoid it. How can I make it work?`

Answer 1

As discussed in the original question, the reason you are getting this error is that the CASE expression is missing its END.