-1
cmd.CommandText = "DELETE * FROM dbo.DisplayData"
cmd.ExecuteNonQuery()

I have the following delete statement at the moment, it's throwing an error at me when it's executed "Incorrect syntax near '*'".

What I'm trying to do is delete all the rows in the DisplayData table then I insert new data, as far as I was aware this was the correct syntax for a SQL delete all statement?

I've looked around online and can't find anything that says my syntax is incorrect. And I can't find a solution.

Thanks in advance.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
BoogaWooga
  • 108
  • 7
  • 9
    Remove the `*`.. – Aethan Feb 22 '16 at 02:29
  • @CrushSundae Perfect thanks, but, at the risk of sounding like an idiot... why don't I need a '*' I thought this was what you needed to reference all rows/columns that fit the query? – BoogaWooga Feb 22 '16 at 02:37
  • 1
    That was used for `SELECT` statements. I advise you to read first the basic fundamentals of querying. :) – Aethan Feb 22 '16 at 02:39
  • Here was me thinking I knew them lol, as far as I remember that's how I was taught in first year using SQL-Server, checked w3Schools and it has the same thing there... oh well, fixed now, thanks – BoogaWooga Feb 22 '16 at 02:42
  • I knew it because I did the same thing before when I was just starting. lol. – Aethan Feb 22 '16 at 02:44
  • To be fair, some SQL systems do allow `DELETE *`, but since you delete an entire row, specifying columns is not necessary, and so `*` has no effect on the statement. – D Stanley Feb 22 '16 at 03:21
  • @DStanley That's the explanation I was looking for, thanks :) – BoogaWooga Feb 22 '16 at 04:32

1 Answers1

5

What you're looking for is:

cmd.CommandText = "DELETE FROM dbo.DisplayData"
cmd.ExecuteNonQuery()

OR

cmd.CommandText = "TRUNCATE dbo.DisplayData"
cmd.ExecuteNonQuery()

Note: truncate will reset the auto increment

Here's a good article about it.

Brian
  • 5,069
  • 7
  • 37
  • 47
Kellen Stuart
  • 7,775
  • 7
  • 59
  • 82
  • 2
    Be careful when you are using `TRUNCATE`. This will reset all the seeds of your auto-increment IDs. This will totally **RESET** your table. – Aethan Feb 22 '16 at 02:33
  • 3
    This is probably what the OP really needs. `TRUNCATE` is faster than `DELETE`; it doesn't log the individual transactions; and it doesn't call delete triggers. – Gordon Linoff Feb 22 '16 at 02:35