0

I want to delete (for example) top 5 rows in my SQL Server database. I used this code shown below, but it does not work. How may I get this?

    Dim con As New SqlConnection(connectionString)

    Using cmd = New SqlCommand()
        cmd.CommandText = "DELETE From PubTb TOP 5"
        cmd.Connection = con
        con.Open()
        Dim numberDeleted As Integer = cmd.ExecuteNonQuery()
        con.Close()
    End Using
TomTom
  • 61,059
  • 10
  • 88
  • 148
Javad-M
  • 456
  • 6
  • 22
  • What do you mean "it does not work"? It works, it just does not work as you expect. So, please be clear on exactly what it does right now, what you want it to do, and what is the error you receive, if any. – jitendragarg May 04 '16 at 05:03
  • What do you mean by ASP.NET SQL? That does not exist. ASP.NET makes web pages, the rest of the .NET framework other things. Whether you use ASP.NET or not for your web pages, ADO.NET is still the technology you use for database manipulation. Be exact and learn not do mix up unrelated terms. – TomTom May 04 '16 at 05:04
  • Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example. – TomTom May 04 '16 at 05:50

3 Answers3

3

See this link

Change it as per your table and Database name. Also order it if you want to

  cmd.CommandText = ";WITH CTE AS ( SELECT TOP 5 * FROM PubTb )  DELETE FROM CTE"
Community
  • 1
  • 1
Nouman Bhatti
  • 1,341
  • 6
  • 28
  • 54
  • 2
    `TOP 5` without specifying an `ORDER BY` clause is useless and dangerous, since you'll be deleting 5 **arbitrary** rows - not ordered by anything...... – marc_s May 04 '16 at 05:49
  • How should I use `ORDER BY` in it? Order by one of my column? – Javad-M May 05 '16 at 04:28
  • May I use this? ` llike: cmd.CommandText = `;WITH CTE AS ( SELECT TOP 5 * FROM PubTb ORDER BY DtTimEntered) DELETE FROM CTE` – Javad-M May 05 '16 at 04:39
1

Use subquery to select desired top rows based on any of the column. then delete such as

DELETE FROM PubTb WHERE ID IN (SELECT TOP 5 ID FROM PubTb ORDER BY ID DESC)
Muhammed Shevil KP
  • 1,404
  • 1
  • 16
  • 21
0

there is no need for a CTE there.

DELETE From PubTb 
FROM PubTb original INNER JOIN
(SELECT TOP 5 ID FROM PubTb /* you may order it */) topfive
ON original.ID = topfive.ID
sapi
  • 244
  • 1
  • 9