CODEWITHSUNDEEP
c#databasems-access
Alex
Alex

Reputation: 21

DELETE and ACCESS

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.

Upvotes: 2

Views: 1379

Answers (7)

rrrhys
rrrhys

Reputation: 654

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).

Upvotes: 1

Lucretius
Lucretius

Reputation: 1049

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.

Upvotes: 0

Don Kirkby
Don Kirkby

Reputation: 56620

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*\"

Upvotes: 0

MusiGenesis
MusiGenesis

Reputation: 75296

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.

Upvotes: -1

cmsjr
cmsjr

Reputation: 59165

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.

Upvotes: 1

David M
David M

Reputation: 72850

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

Upvotes: 3

Arseny
Arseny

Reputation: 7351

pay attention on quotation marks LIKE 'something*'

Upvotes: 0

Related Questions