SQL ORDER BY更新-使用条件更新最后一条记录 [英] SQL ORDER BY on Update- update last record with condition

问题描述

鉴于TimeExited为null，并且计算机名与"cm"参数相同，我正在尝试更新表Log的最后一条记录(具有最新的TimeAccessed的字段).我有这个但得到错误，在SQL语句末尾缺少分号"

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"

怎么了?

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

推荐答案

Access SQL不使用LIMIT n，而是使用TOP n，如

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
        )

这篇关于SQL ORDER BY更新-使用条件更新最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！