在SQL Server中删除表中的“第一个”记录，而没有WHERE条件

Is it possible to delete the 'first' record from a table in SQL Server, without using any WHERE condition and without using a cursor?

是否可以从SQL Server中的表中删除“first”记录，而不使用任何WHERE条件，也不使用游标?

8 个解决方案

#1

119

WITH  q AS
        (
        SELECT TOP 1 *
        FROM    mytable
        /* You may want to add ORDER BY here */
        )
DELETE
FROM    q

Note that

请注意,

DELETE TOP (1)
FROM   mytable

will also work, but, as stated in the documentation:

也将有效，但如文件所述:

The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

与插入、更新或删除一起使用的顶部表达式中引用的行不按任何顺序排列。

Therefore, it's better to use WITH and an ORDER BY clause, which will let you specify more exactly which row you consider to be the first.

因此，最好使用WITH和ORDER BY子句，这样可以让您更准确地指定您认为是第一行的行。

#2

depends on your DBMS (people don't seem to know what that is nowadays)

取决于你的数据库管理系统(现在人们似乎不知道它是什么)

-- MYSql:
DELETE FROM table LIMIT 1;
-- Postgres:
DELETE FROM table LIMIT 1;
-- MSSql:
DELETE TOP(1) FROM table;
-- Oracle:
DELETE FROM table WHERE ROWNUM = 1;

#3

No, AFAIK, it's not possible to do it portably.

不，阿法克，这是不可能的。

There's no defined "first" record anyway - on different SQL engines it's perfectly possible that "SELECT * FROM table" might return the results in a different order each time.

没有定义的“first”记录——在不同的SQL引擎上，很有可能“SELECT * FROM table”每次都以不同的顺序返回结果。

#4

Define "First"? If the table has a PK then it will be ordered by that, and you can delete by that:

定义“第一”?如果该表有PK，则按PK排序，可以删除:

DECLARE @TABLE TABLE
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Data NVARCHAR(50) NOT NULL
)

INSERT INTO @TABLE(Data)
SELECT 'Hello' UNION
SELECT 'World' 

SET ROWCOUNT 1
DELETE FROM @TABLE
SET ROWCOUNT 0

SELECT * FROM @TABLE

If the table has no PK, then ordering won't be guaranteed...

如果表没有PK，那么就不能保证排序…

#5

Does this really make sense?
There is no "first" record in a relational database, you can only delete one random record.

这真的有意义吗?关系数据库中没有“第一”记录，只能删除一条随机记录。

#6

What do you mean by «'first' record from a table» ? There's no such concept as "first record" in a relational db, i think.

您说的«'first'记录来自表格是什么意思?我认为在关系数据库中不存在“第一个记录”这样的概念。

Using MS SQL Server 2005, if you intend to delete the "top record" (the first one that is presented when you do a simple "*select * from tablename*"), you may use "delete top(1) from tablename"... but be aware that this does not assure which row is deleted from the recordset, as it just removes the first row that would be presented if you run the command "select top(1) from tablename".

使用MS SQL Server 2005，如果您打算删除“top record”(在执行简单的“*select * from tablename*”时出现的第一个记录)，您可以使用“delete top(1) from tablename”……但是要注意，这并不能确保从记录集中删除哪个行，因为它只是删除了如果您运行命令“从tablename中选择top(1)”时将会出现的第一行。

#7

Similar to the selected answer, a table source can be used, in this case a derived query:

与所选答案类似，可以使用表源，在这种情况下，可以使用派生查询:

delete from dd
from (
    select top 1 *
    from my_table
) dd

Feel free to add orderbys and conditions.

请随意添加订单和条件。

For the next example, I'll assume that the restriction on 'where' is due to not wanting to select a row based on its values. So assuming that we want to delete a row based on position (in this case the first position):

对于下一个示例，我将假设对“where”的限制是由于不希望根据其值选择行。假设我们要基于位置删除一行(在本例中是第一个位置):

delete from dd
from (
    select
        *,
        row = row_number() over (order by (select 1))
    from my_table
) dd
where row = 1

Note that the (select 1) makes it the sort order that the tables or indexes are in. You can replace that with a newid to get fairly random rows.

注意(select 1)使它成为表或索引所在的排序顺序。您可以用newid替换它以获得相当随机的行。

You can also add a partition by to delete the top row of each color, for example.

例如，您还可以通过删除每个颜色的第一行来添加分区。

#8

-1

SQL-92:

sql - 92:

DELETE Field FROM Table WHERE Field IN (SELECT TOP 1 Field FROM Table ORDER BY Field DESC)

#1

119

WITH  q AS
        (
        SELECT TOP 1 *
        FROM    mytable
        /* You may want to add ORDER BY here */
        )
DELETE
FROM    q