inner join without unique identifier-CodePudding

On the screenshot below you see two tables. On the left side I have a table with accidents, in this case the ID is unique and it is always the same accident with ID=68.

On the right side is the weather data available with the temperature of the specific day.

However, the date is not uniquely identifiable since there exists five Saturday's in January in my case. What I am trying to do, is to write the temperature in my first "Accident" table (empty column).

I know that there is a problem since I don't want to have five times the same accident in my table (id=68 should remain uniquely). Is there a way to join this data even if the temperature will not be 100% correct? I am happy with an average of the temperature, or just use the first entry.

This is my query, it never stops to run.

UPDATE accident_copy as a
LEFT JOIN wetterdaten as w
ON a.year = w.year
    and a.monthDE = w.month
    and a.day = w.day
    and a.hour = w.hour
SET a.Temperatur = w.Temperatur;

CodePudding user response：

Instead use a subquery to get your average temperature for that time:

UPDATE accident_copy as a
LEFT JOIN 
   (
      SELECT avg(temperatur) temparatur, year, month, day, hour 
      FROM wetterdaten
      GROUP BY year, month, day, hour
    ) as w
ON a.year = w.year
    and a.monthDE = w.month
    and a.day = w.day
    and a.hour = w.hour
SET a.Temperatur = w.Temperatur;