SQLTeam.com | Weblogs | Forums

Update same table each row based on criteria


#1

I am trying to update a column (DerdeLaasteSuiker) with (Suiker) for each plaasblok where Thirdlast = Datum. Ihave the following query :

   UPDATE t 
    SET t.DerdeLaasteSuiker = t2.Suiker 
    FROM Ontledings t 
    INNER JOIN Ontledings t2 ON t.Plaasblok = t2.Plaasblok 
   WHERE t2.Thirdlast = t.Datum

Sample data with the desired result in the last column

Datum Suiker Plaasblok Last Secondlast Thirdlast DerdeLaasteSuiker
01/02/2017 21.1 113321-MON 09/02/2017 08/02/2017 01/02/2017 21.1
08/02/2017 24.6 113321-MON 09/02/2017 08/02/2017 01/02/2017 21.1
09/02/2017 24.6 113321-MON 09/02/2017 08/02/2017 01/02/2017 21.1
27/02/2017 20.2 1133210-MON 27/02/2017 NULL NULL NULL
21/02/2017 20 1133212-MON 27/02/2017 21/02/2017 NULL NULL
27/02/2017 20.3 1133212-MON 27/02/2017 21/02/2017 NULL NULL
21/02/2017 19.6 1133213-MON 27/02/2017 21/02/2017 NULL NULL
27/02/2017 21.2 1133213-MON 27/02/2017 21/02/2017 NULL NULL
21/02/2017 19.4 1133214-MON 27/02/2017 21/02/2017 NULL NULL
27/02/2017 20.4 1133214-MON 27/02/2017 21/02/2017 NULL NULL
27/02/2017 22.5 1133215-MON 27/02/2017 NULL NULL NULL
27/02/2017 21.5 1133216-MON 27/02/2017 NULL NULL NULL
27/02/2017 16.5 1133217-MON 15/03/2017 09/03/2017 27/02/2017 16.5
09/03/2017 17.3 1133217-MON 15/03/2017 09/03/2017 27/02/2017 16.5
15/03/2017 18.9 1133217-MON 15/03/2017 09/03/2017 27/02/2017 16.5
31/01/2017 20.3 11373A1B 23/02/2017 16/02/2017 08/02/2017 23.2
08/02/2017 23.2 11373A1B 23/02/2017 16/02/2017 08/02/2017 23.2
16/02/2017 22.7 11373A1B 23/02/2017 16/02/2017 08/02/2017 23.2
23/02/2017 24.8 11373A1B 23/02/2017 16/02/2017 08/02/2017 23.2
23/02/2017 21.9 11373A3 10/03/2017 06/03/2017 27/02/2017 24.4
27/02/2017 24.4 11373A3 10/03/2017 06/03/2017 27/02/2017 24.4
06/03/2017 23.6 11373A3 10/03/2017 06/03/2017 27/02/2017 24.4
10/03/2017 24.7 11373A3 10/03/2017 06/03/2017 27/02/2017 24.4

I get a zero for the result. Any help would be appreciated.


#2

Change

WHERE t2.Thirdlast = t.Datum

to

WHERE t.Thirdlast = t2.Datum

#3

Thank you. That worked.