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

Posted by UkraineTrain on Stack Overflow See other posts from Stack Overflow or by UkraineTrain
Published on 2010-06-05T07:24:59Z Indexed on 2010/06/05 7:32 UTC
Read the original article Hit count: 165

Filed under:
|

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?`

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-query