Left Join Problem

Discussion in 'MySQL' started by Jamie T, Aug 4, 2012.

0
  1. #1
    Hello. I have this Left Join to return an average value, it works well like this:


    SELECT ((watertemp.reading- t2.reading) / DATEDIFF(watertemp.date, t2.date)) as avg
    FROM watertemp
    LEFT JOIN watertemp AS t2 ON t2.id = watertemp.id- 1


    BUT, I want to use it to update a table with a where clause. My query faults out. Obviously a fault with my where clause.
    Can somebody please advise me what I'm doing wrong?

    update water(avg) set
    (SELECT ((watertemp.reading- t2.reading) / DATEDIFF(watertemp.date, t2.date)) as avg
    FROM watertemp
    LEFT JOIN watertemp AS t2 ON t2.id = watertemp.id- 1)
    where watertemp.event_id = water.event_id

    ('watertemp' is a temporary table storing water usage readings. I want to calculate the average per day then insert that into the main 'water' readings table.)

    Many Thanks.

    Jamie.
     
    Jamie T, Aug 4, 2012 IP
  2. Calixarene

    Calixarene Greenhorn

    Messages:
    21
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #2
    You've aliased watertemp AS t2 then used the table name in full in your WHERE clause. It might be that which it causing it to throw a wobbly. worth a shot.
     
    Calixarene, Aug 20, 2012 IP