By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

Delete TOP X number of records

P: n/a
Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
PaulMac wrote:
Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks


Without trying, dunno: delete top 3 * from mytable?

If not then make a query, e.g. qryMyTop3 as select top 3 blah blah then
use another query to delete * from qryMyTop3.

--
But why is the Rum gone?
Nov 12 '05 #2

P: n/a
thanks for responding Trevor.

delete top 3 * from mytable...doesn't work (I'm using an .adp if that
makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i want to
delete is a variable that is populated from within code...

Trevor Best <nospam@localhost> wrote in message news:<40**********************@auth.uk.news.easyne t.net>...
PaulMac wrote:
Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks


Without trying, dunno: delete top 3 * from mytable?

If not then make a query, e.g. qryMyTop3 as select top 3 blah blah then
use another query to delete * from qryMyTop3.

Nov 12 '05 #3

P: n/a
pa********@hotmail.com (PaulMac) wrote in
news:1e**************************@posting.google.c om:
delete top 3 * from mytable...doesn't work (I'm using an .adp if
that makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i
want to delete is a variable that is populated from within code...


First off, TOP 3 is going to be randomly selected unless you include
a non-ambiguous ORDER BY clause (i.e., one that can produce only one
definitive sort order for any group of records).

If you have a variable N for TOP N, you have to do one of two
things:

1. construct the SQL in code and execute it in code.

OR

2. in code, save a stored querydef with the appropriate N value and
execute that.

I see absolutely no reason to do #2 and would also do #1.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #4

P: n/a
PaulMac wrote:
thanks for responding Trevor.

delete top 3 * from mytable...doesn't work (I'm using an .adp if that
makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i want to
delete is a variable that is populated from within code...


ADP makes a difference, use a stored procedure.

create procedure DeleteMyTopN
@paramHowMany int
as
set rowcount @paramHowmany
delete from MyTable

You can call this using a adodb.command object and set the parameter.
Nov 12 '05 #5

P: n/a
pa********@hotmail.com (PaulMac) wrote in message news:<1e**************************@posting.google. com>...
thanks for responding Trevor.

delete top 3 * from mytable...doesn't work (I'm using an .adp if that
makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i want to
delete is a variable that is populated from within code...

Trevor Best <nospam@localhost> wrote in message news:<40**********************@auth.uk.news.easyne t.net>...
PaulMac wrote:
Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks


Without trying, dunno: delete top 3 * from mytable?

If not then make a query, e.g. qryMyTop3 as select top 3 blah blah then
use another query to delete * from qryMyTop3.


If you want to change the number of records that get deleted each time
(so say X can take any non-negative value), you would need to create a
string to build the SQL statement and then execute it.

Sub DeleteNRecords(byval intNumRecords As Long)

Dim strSQL as string
strSQL = "DELETE TOP " & intNumRecords & " FROM MyTable ORDER BY
MyField;"
CurrentDB.Execute strSQL, dbFailOnError

End Sub
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.