Can't fill a column of NULLs with actual values by making an association to the proper values in the reference table.

Question

I have a table with separate columns for months, days and a varchar column for 6 hour increments for each day ('12AM', '6AM', '12PM', '6PM'). There's also a column that's supposed to have calculated numeric values for each of those 6 hour increments. These calculated values come from some reference table. This reference table contains values for each day for several months broken down by hour where each hour has its own column. So, basically, I have to add the values for each 6 hour increment. I have no idea how to associate the correct values in the reference table to those 6 hour increments.

I will really appreciate any help on this.

Answer 1

You could try something like:

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+...
     END)
  WHERE main_table.month = reference_table.month
    AND main_table.day = reference_table.day