开发者

How do I delete all but the newest 20 rows in a table?

How do you delete the top row? I want to delete the top row if theres more 开发者_如何学JAVAthan 20 rows. im using c#.


Have a look at DELETE (Transact-SQL)

TOP (expression) [ PERCENT ]

Specifies the number or percent of random rows that will be deleted. expression can be either a number or a percent of the rows. The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

Maybe also have a read at The DELETE statement in SQL Server


Some info about deletion http://msdn.microsoft.com/en-us/library/bb386925.aspx

var ordFetch =
    (from ofetch in db.Orders
     where ofetch.OrderID == reqOrder
     select ofetch).First();
db.Orders.DeleteOnSubmit(ordFetch);
db.SubmitChanges();

To select top rows:

var data = (from p in people
           select p).Take(100);


Assuming the ID field is a suitable substitute and always increases with time (i.e., smallest values of ID are the oldest, and that is always the case):

using (SqlConnection conn = new SqlConnection("yourconnectionstring"))
{
    SqlCommand cmd = conn.CreateCommand(
        "DELETE FROM table WHERE ID NOT IN (SELECT TOP 20 ID FROM table ORDER BY ID DESC)");
    cmd.ExecuteNonQuery();
}

(My C# syntax may be slightly off -- I did this from memory -- but the SQL query should be fine.)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜