TOP(1) in update query?

  • Thread starter Jesper Fjølner
  • Start date
J

Jesper Fjølner

I'm having problems with "top(1)" part of this SQL. Can I use the TOP word
in an update-query?

UPDATE TOP(1) tblLog SET exittime = #" & Time & "# WHERE username = '" &
CurrentUser & "'"

Running this with db.execute
 
D

Douglas J. Steele

No, you can't.

If you're trying to set the value for a specific row in tblLog, you're going
to have to determine some way of identifying that specific row.
 
J

Jesper Fjølner

If you're trying to set the value for a specific row in tblLog, you're
going
to have to determine some way of identifying that specific row.
Click to expand...

I'll figure it out another way. Thanks!
 
J

John Spencer (MVP)

How about something similar to the following untested string.

"UPDATE tblLog SET exittime = #" & Time & "# " & _
"WHERE tblLog.PrimaryKey in " & _
"(SELECT Top 1 A.PrimaryKey FROM tblLog " & _
" WHERE Username = '" & CurrentUser & "' " & _
" ORDER BY PrimaryKey)"

You need to set the order by clause to reflect which fields determine which
record should be the first record.
 
Top