DELETE and ACCESS

Question

Where is the error in this statement?

query = "DELETE TOP 10 FROM table WHERE field LIKE \"something*\""

I get an error on the query sytax.

Thanks.

Answer 1

pay attention on quotation marks LIKE 'something*'

Answer 2

You can't use TOP with DELETE. You must identify the rows, then delete them.

Answer 3

Try

query = "DELETE * from (Select TOP 10  * FROM table WHERE field LIKE \"something*\")"

While you can't directly use top with Delete, you can use it for a derived table, and then Delete from the derived table.

Answer 4

This may or may not be possible depending on what database you're using, but you may be able to write your query something like this:

DELETE FROM TBLWHATEVER WHERE FLDWHATEVER LIKE 'something%' AND ROWNUM < 10

It depends on whether your DB has a query function like ROWNUM.

Answer 5

If you can't do all the deletes at once, and moving to a more scalable database isn't an option, then you might try partitioning the data based on some other field and doing multiple deletes.

Let's assume you have a field called name and it's distribution is roughly even throughout the alphabet. You could do multiple deletes like this:

query0 = "DELETE FROM table WHERE field LIKE \"something*\" and name <= \"D\""
query1 = "DELETE FROM table WHERE field LIKE \"something*\" and name <= \"H\""
query2 = "DELETE FROM table WHERE field LIKE \"something*\" and name <= \"L\""
query3 = "DELETE FROM table WHERE field LIKE \"something*\" and name <= \"P\""
query4 = "DELETE FROM table WHERE field LIKE \"something*\" and name <= \"T\""
query5 = "DELETE FROM table WHERE field LIKE \"something*\" and name <= \"X\""
query6 = "DELETE FROM table WHERE field LIKE \"something*\"

Answer 6

You could make this work using a nested query like this...

---

DELETE FROM [TABLE] WHERE [Col1] = (
SELECT TOP 10 [Col1] FROM [TABLE] WHERE [criteria] ORDER BY [criteria]
);

Note in the subquery you're simply feeding a list into the main delete query by specifying what you want to delete from Col1, and the more criteria you have the fewer resources you'll need because more options for deletion will be eliminated.

To test this first Omit the DELETE FROM portion of the syntax and just run the query so you can see what you'll be feeding into your DELETE statement like this...

SELECT TOP 10 [Col1] FROM [TABLE] WHERE [criteria] ORDER BY [criteria]

Its important that you use the ORDER BY clause in case your sub query returns more than 10 results, that way you have a higher degree of control over what you're deleting.

Answer 7

Try

Delete * from [tablename] where ID in (Select Top 10 ID from [Tablename] Where [Field] Like '*Condition*'

This way, you aren't looking up * (everything) in * (everything).