SQL ORDER BY on Update- update last record with condition

Question

I am trying to update the last record of table Log (the field with the latest TimeAccessed) given that TimeExited is null, and the computername is the same as the "cm" parameter. I have this but get error, "missing semicolon at end of sql statement"

what is wrong??

dbs.Execute "UPDATE Log " _
& "SET TimeExited = " & Format(CloseTime, "\#hh:mm:ss AMPM\#") _
& " WHERE TimeExited is NULL AND ComputerName = '" & cm & "'" _
& " ORDER BY TimeAccessed DESC" _
& " LIMIT 1; "

nothing wrong with first 2 lines, work perfectly fine, it's the last two that give problems

http://stackoverflow.com/questions/20539095/update-top-1-record-in-table-sql-server — TTT, Oct 15 '14 at 17:43

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

Access SQL doesn't use LIMIT n it uses TOP n, and as mentioned in the other question cited in the comments to your question, you aren't allowed to use TOP in the way you've described. Instead, you'll need to do something along these lines:

UPDATE Log 
SET TimeExited = CloseTime
WHERE TimeExited IS NULL 
    AND ComputerName='r2d2'
    AND TimeAccessed IN
        (
            SELECT TOP 1 TimeAccessed
            FROM Log
            WHERE TimeExited IS NULL 
                AND ComputerName='r2d2'
            ORDER BY TimeAccessed DESC
        )

edited May 23 '17 at 10:33

Community

1
1

answered Oct 15 '14 at 18:09

Gord Thompson

116,920
32
215
418

you are right, I did not search enough. This solved it, thanks – user2006562 Oct 15 '14 at 18:33

SQL ORDER BY on Update- update last record with condition

1 Answers1

Linked

Related