SQL Update Query

Question

I am not bad with SQL but I am having a hard time figuring this one out.

I have a table called test, with a character column called key and two datetime columns called TestTime and LastTestTime.

I am trying to write a query that will update all the rows and set the value of LastTestTime to the most recent previous TestTime for that same key.

Here is an example of how I want this to turn out:

key   testTime   lastTestTime
------------------------------
aaa   1/1/2012   null
aaa   1/2/2012   1/1/2012
aaa   1/3/2012   1/2/2012

Answer 1

None of these answered the question. The questioner wants the most recent test time before each test, not the overall most recent:

UPDATE test 
    SET lastTestTime = (SELECT TOP(1) testTime
                        FROM test tt
                        WHERE test.key = tt.key AND tt.TestTime < test.TestTime
                        ORDER BY testTime DESC
                       )              

Answer 2

UPDATE t
SET LastTestTime = t2.TestTime
FROM
    Test t JOIN 
    Test t2 
        ON t2.[Key] = t.[Key]
        AND t2.TestTime = (
            SELECT MAX(t3.TestTime)
            FROM Test t3
            WHERE 
                t3.TestTime < t.TestTime
                AND t3.[Key] = t.[Key]
        )