0

I effectively want to run an UPDATE command on a table in an Access database (via ADO.NET) but I want it to run backwards - ie from the last record to the first - to avoid duplicate records with the same 'ID'.

I tried adding & " ORDER BY ID DESC;" but this throws an error as its not allowed.

Help!

Thanks Andy

Dim dsxcmd As OleDbCommand

strSelect = "UPDATE Items SET ID=ID+1 WHERE ID>=" & lInsert.ToString 
dsxcmd = New OleDbCommand(strSelect, cn)
dsxcmd.ExecuteNonQuery()
dsxcmd.Dispose()
Andy Powell
  • 583
  • 1
  • 6
  • 15

2 Answers2

6

SQL is declarative. So if you want to order the updates in a defined order you need to do it cursor operation (one row at a time) - using a record set then read a row/write a row kind of loop. I don't think Access has server side cursors so this will have to be done client side.

However if you want to update many rows at the same time then you need to update them to a non clashing range first.

For instance - make the ID's negative first

UPDATE Items SET ID=ID * -1 WHERE ID>=...

Then update the

UPDATE Items SET ID=(ID *-1) + 1 WHERE ... change where to pick up the new range
Preet Sangha
  • 64,563
  • 18
  • 145
  • 216
-1

Why not create a recordset and loop through that? Update is set-based, so you don't have step by step control on the records like a procedural language.

JeffO
  • 7,957
  • 3
  • 44
  • 53
  • 1
    I didn't know you could use CTE's on Access? http://stackoverflow.com/questions/763016/is-it-possible-to-create-recursive-query-in-access – Preet Sangha Dec 23 '13 at 02:09
  • Don't know why I thought this was SQL Server connected through Access. – JeffO Dec 24 '13 at 16:17