delete fixed numbers of rows in table

[sal21]

I need via Sql query to delete a block of 1000 records in mytable with this condition:

...where myfield='1245' order by myfield

my sql but not work:-(
dELETE TOP (1000) FROM myfield WHERE myfield='1245'

[HansV]

The Top keyword only works with select queries, not with action queries such as a delete query.

Does your table have a primary key field, for example an AutoNumber field? If so, you can do it like this (the primary key is named ID in this example):

DELETE ID FROM MyTable WHERE ID In (SELECT TOP 1000 ID FROM MyTable WHERE MyField='1245' ORDER BY ID)

[sal21]

The Top keyword only works with select queries, not with action queries such as a delete query.

Does your table have a primary key field, for example an AutoNumber field? If so, you can do it like this (the primary key is named ID in this example):

DELETE ID FROM MyTable WHERE ID In (SELECT TOP 1000 ID FROM MyTable WHERE MyField='1245' ORDER BY ID)

no primary key :-(

[HansV]

You can still specify another sort order, but you won't be able to specify exactly which 1000 records will be deleted:

DELETE AnyField FROM MyTable WHERE AnyField In (SELECT TOP 1000 AnyFielD FROM MyTable WHERE MyField='1245' ORDER BY AnyField)

[sal21]

You can still specify another sort order, but you won't be able to specify exactly which 1000 records will be deleted:

DELETE AnyField FROM MyTable WHERE AnyField In (SELECT TOP 1000 AnyFielD FROM MyTable WHERE MyField='1245' ORDER BY AnyField)

OK NOW WORK FINE!

But my really prob is to delete a lots row in table based a condition where...
Approx are 234.000 in the access table. With the tipical delete * from mytab where....ecc the code use very big time!

I have think.. if i use a loop with for next and increment the top statment by a counter (to delete a blocks of 1000 rows) similar top=top+1000 ecc, the deletation operation is very fast, or not?

other way are welcome.

[HansV]

I don't think deleting 50000 records in 50 steps of 1000 will be faster than deleting them all at once.

Is your database stored on a network drive? If so, try this:
- Copy the database to a local hard disk.
- Run a time-consuming query.
- Copy the database back to the network drive.

[sal21]

I don't think deleting 50000 records in 50 steps of 1000 will be faster than deleting them all at once.

Is your database stored on a network drive? If so, try this:
- Copy the database to a local hard disk.
- Run a time-consuming query.
- Copy the database back to the network drive.

No is in local c:\....

note:
time-consuming query (?)

[HansV]

Time consuming = something that takes a lot of time.

You'll have to live with the problem (or move your data to SQL Server on a powerful server)

[sal21]

Time consuming = something that takes a lot of time.

You'll have to live with the problem (or move your data to SQL Server on a powerful server)

AHHHHHHHHHHHH......

i test to move on SqlExpress
as usual.