Update top X records using SQL in VBA

[dp88]

Hi All

I'm trying to run an SQL query in VBA that will update the top 5 records only

Below is what I have, however I keep getting an error saying SQL needs a semi colon (which I have).

Would anyone know how I can update only the top x records with a WHERE clause?

Thanks all for you help!

Sub updateuserid2()

'Updatecaserecords

Dim SQL As String

SQL = "UPDATE Cases SET Cases.Status = 'Assigned' WHERE Cases.Status ='UNALLOCATED' ORDER BY Cases.Status LIMIT 5;"

DoCmd.RunSQL SQL


End Sub

 

[plog]

LIMIT isn't part of Access SQL. TOP is, but you can't use it in an UPDATE query. You can however build an updateable SELECT query and UPDATE it:

SELECT TOP 5 Status FROM Cases WHERE Status="UNALLOCATED"

Save that as 'sub1'. Then..

UPDATE sub1 SET Status="Assigned"

 

[CJ_London]

I presume you are talking about access sql, not sql server. LIMIT is not a recognised keyword in Access sql, you need to use the TOP keyword in a subquery in conjunction with a unique value such as a PK- something like

UPDATE Cases SET Status = 'Assigned' WHERE CasePK IN (SELECT TOP 5 CasePK FROM Cases WHERE Status ='UNALLOCATED')

 

[dp88]

I presume you are talking about access sql, not sql server. LIMIT is not a recognised keyword in Access sql, you need to use the TOP keyword in a subquery in conjunction with a unique value such as a PK- something like

UPDATE Cases SET Status = 'Assigned' WHERE CasePK IN (SELECT TOP 5 CasePK FROM Cases WHERE Status ='UNALLOCATED')

Thank you CJ_London. Unfortunately it's access sql not sql server. I didn't realise LIMIT was available on Access sql.

Your response works absolutely perfectly!! Thank you so much for your help!!

 

[dp88]

LIMIT isn't part of Access SQL. TOP is, but you can't use it in an UPDATE query. You can however build an updateable SELECT query and UPDATE it:

SELECT TOP 5 Status FROM Cases WHERE Status="UNALLOCATED"

Save that as 'sub1'. Then..

UPDATE sub1 SET Status="Assigned"

Thank for your response Plog, I appreciate it. That would work but I think that CJ_London's approach is perhaps a little cleaner although I do appreciate you sharing your idea!!!