How to update last record with second lat results [SQLServer]

Question

I want to update the last entry of the table with Pass/Fail result from the second last row. And the query need to didnt fail if only one row is in the table.

Here is the code what i have, but ist only update with the first entrys and not the the second last entrys. thx for help

UPDATE DB.dbo.Testergebnisse 
SET 
    Pass = (
        SELECT TOP 1 Pass
        FROM DB.dbo.Testergebnisse
        WHERE 
            TestergebnisID != ( 
                SELECT MAX(TestergebnisID) FROM DB.dbo.Testergebnisse 
            )
            AND TestaufstellungID = 3166 
        ORDER BY TestergebnisID DESC
    ),
    Fail = (
        SELECT TOP 1 Fail
        FROM DB.dbo.Testergebnisse
        WHERE 
            TestergebnisID != ( 
                SELECT MAX(TestergebnisID)  FROM DB.dbo.Testergebnisse 
            ) 
            AND TestaufstellungID = 3166 
            ORDER BY TestergebnisID DESC
    )
WHERE 
    DB.dbo.Testergebnisse.TestergebnisID = ( 
        SELECT TOP 1 TestergebnisID 
        FROM DB.dbo.Testergebnisse 
        WHERE TestaufstellungID =  3166 
        ORDER BY TestergebnisID DESC
    )

SQL Server Version: 9.0.5057

Answer 1

I'm not sure I'm following. Given data like this:

TestergebnisID TestaufstellungID Pass        Fail
-------------- ----------------- ----------- -----------
1              1                 10          12
2              1                 5           6
3              1                 3           4
4              2                 0           0

You want to update the row with TestergebnisID = 3 with Pass and Fail from record with TestergebnisID = 2 and that's it? Or should record 2 be updated with data from record 1 too?

In case of the former, this should do the trick:

;WITH rec AS
(
    SELECT this.TestaufstellungID 
         -- most recent for the given test run
         , MAX(this.TestergebnisID) AS LastTestergebnisID 
         -- find the previous 
         , PrevTestergebnisID =
           (SELECT MAX(prev.TestergebnisID) 
              FROM Testergebnisse prev 
             WHERE prev.TestaufstellungID = this.TestaufstellungID  
               AND prev.TestergebnisID < MAX(this.TestergebnisID )
           ) 
      FROM Testergebnisse this
     GROUP BY this.TestaufstellungID
)
UPDATE mostRecent
   SET Pass = prev.Pass
     , Fail = prev.Fail
  FROM Testergebnisse mostRecent
  JOIN rec
    ON rec.LastTestergebnisID = mostRecent.TestergebnisID 
  JOIN Testergebnisse prev
    ON prev.TestergebnisID = rec.PrevTestergebnisID 

Gives:

TestergebnisID TestaufstellungID Pass        Fail
-------------- ----------------- ----------- -----------
1              1                 10          12
2              1                 5           6
3              1                 5           6       <-- 1 row affected
4              2                 0           0

If you want all of them to be updated with their predecessors' values, then:

;WITH rec AS
(
    SELECT this.TestaufstellungID 
         -- most recent for the given test run
         , this.TestergebnisID
         -- find the previous 
         , PrevTestergebnisID =
           (SELECT MAX(prev.TestergebnisID) 
              FROM Testergebnisse prev 
             WHERE prev.TestaufstellungID = this.TestaufstellungID  
               AND prev.TestergebnisID < this.TestergebnisID
           ) 
      FROM Testergebnisse this
)
UPDATE mostRecent
   SET Pass = prev.Pass
     , Fail = prev.Fail
  FROM Testergebnisse mostRecent
  JOIN rec
    ON rec.TestergebnisID = mostRecent.TestergebnisID 
  JOIN Testergebnisse prev
    ON prev.TestergebnisID = rec.PrevTestergebnisID 

This will update rows 2 (from 1) & 3 (from 2):

TestergebnisID TestaufstellungID Pass        Fail
-------------- ----------------- ----------- -----------
1              1                 10          12
2              1                 10          12       <--
3              1                 5           6        <--
4              2                 0           0