Geek asks and answers

SQL update request

I'm not good at SQL, but it's hard for me to figure out how to do this.

I have a table called testwith a column of characters with a name keyand two columns datetimecalled TestTimeand LastTestTime.

I am trying to write a query that updates all rows and sets the value LastTestTimefor the last previous TestTimefor the same key.

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

key   testTime   lastTestTime
------------------------------
aaa   1/1/2012   null
aaa   1/2/2012   1/1/2012
aaa   1/3/2012   1/2/2012
+3
source share
3 answers

None of them answered the question. The questioner needs the most recent test time before each test, and not the last last:

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
                       )
+2
source 
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]
        )
+1
source 
UPDATE test
SET lastTestTime = 
   (SELECT TOP(1) testTime 
    FROM test tt
    WHERE test.key = tt.key
    ORDER BY testTime DESC)
0

More articles:


All Articles