Hi

You can do a select top 1 * from <table>, my question is
how does one delete the row whithout having to do lots of where
clause ?
Is there a equivelant delete top 1 from <table>

Thanks

<rc@die@you@!spammers.sandworm.demon.co.uk> wrote in message
news:vol9q094ehtd9d5n9c5rab726cr17s05m2@4ax.com...
> Hi
>
> You can do a select top 1 * from <table>, my question is
> how does one delete the row whithout having to do lots of where
> clause ?
> Is there a equivelant delete top 1 from <table>
>
> Thanks
>
You can use "SET ROWCOUNT" to limit the number of rows affected by the
subsequent statement.

> Is there a equivelant delete top 1 from <table>

How useful would that be? If you want to delete something you usually want
to delete something specific:

DELETE FROM YourTable
WHERE x = 1234

Tables of course have no inherent order so to perform an operation on "TOP
1" of anything without specifying any other criteria is both meaningless and
dangerous.

--
David Portas
SQL Server MVP
--

On Wed, 24 Nov 2004 18:49:09 +0000,
rc@die@you@!spammers.sandworm.demon.co.uk wrote:

> Hi
>
> You can do a select top 1 * from <table>, my question is
> how does one delete the row whithout having to do lots of where
> clause ?
> Is there a equivelant delete top 1 from <table>
>
> Thanks

The best way is

DELETE FROM tbl WHERE tbl.pkey = (SELECT TOP 1 pkey FROM tbl)

It's not that much extra typing, and it's clear what is meant.

>> how does one delete the row without having to do lots of WHERE
clause ?<<

In a relational database, you use the key in the WHERE clause. Have
you ever read anything about relational databases?

The idea of using the non-relational, hidden cursor SELECT TOP 1 is
insane. Since tables have no ordering, you don't know what will be in
the PHYSICAL location that is being trashed. This "feature" is a
serious violation of the Relational model.