Friday, February 24, 2012

Delete rows from table

I need a script that will delete the first 100000 rows of a table. Is this possible?There are no 'first' rows because a relational database table is an
unordered set of rows. That said, below are a couple of methods that will
delete up to 100000 rows.

--primary key is MyKey
DELETE FROM MyTable
WHERE MyTable.MyKey
IN
(
SELECT TOP 100000 MyKey
FROM MyTable
ORDER BY MyKey
)

--no primary key
SET ROWCOUNT 100000
DELETE FROM MyTable
SET ROWCOUNT 0

--
Hope this helps.

Dan Guzman
SQL Server MVP

"C.P." <carmenpuccio@.yahoo.com> wrote in message
news:8b8faf60.0402061532.177b1ad0@.posting.google.c om...
> I need a script that will delete the first 100000 rows of a table. Is this
possible?|||>> I need a script that will delete the first 100000 rows of a table.
Is this possible? <<

This actually makes no sense. The basic idea of a table is that it is
an unordered set of values; there is no "first" or "next" or "last";
it is not anything like a sequential file.

Tye next basic idea is that rows are found by logic, not by counting
their **physical** position in **physical** storage.

Please read a book or take an RDSBMS course so you'll have some
foundations.|||joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0402071301.30cd8600@.posting.google.com>...
> >> I need a script that will delete the first 100000 rows of a table.
> Is this possible? <<
> This actually makes no sense. The basic idea of a table is that it is
> an unordered set of values; there is no "first" or "next" or "last";
> it is not anything like a sequential file.
> Tye next basic idea is that rows are found by logic, not by counting
> their **physical** position in **physical** storage.
> Please read a book or take an RDSBMS course so you'll have some
> foundations.

Actually I meant to say the oldest rows and the script that was given
to me is exactly what I needed and I appreciate it. As for you, go to
h*ll and learn how to spell...

No comments:

Post a Comment