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'
delete fixed numbers of rows in table
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: delete fixed numbers of rows in table
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)
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)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
Re: delete fixed numbers of rows in table
no primary key :-(HansV wrote: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)
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: delete fixed numbers of rows in table
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)
DELETE AnyField FROM MyTable WHERE AnyField In (SELECT TOP 1000 AnyFielD FROM MyTable WHERE MyField='1245' ORDER BY AnyField)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
Re: delete fixed numbers of rows in table
OK NOW WORK FINE!HansV wrote: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)
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.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: delete fixed numbers of rows in table
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.
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.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
Re: delete fixed numbers of rows in table
No is in local c:\....HansV wrote: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.
note:
time-consuming query (?)
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: delete fixed numbers of rows in table
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)
You'll have to live with the problem (or move your data to SQL Server on a powerful server)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
Re: delete fixed numbers of rows in table
AHHHHHHHHHHHH......HansV wrote: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)
i test to move on SqlExpress
as usual.