How to randomly delete 1 row from a DB ?

Hello, I'm writing a program with the help of VB 2005 Express. It is connected to a DB using SQL Express Server 2005. In my program I want to randomly delete some rows from the table.
I use the VB code:
Dim WhichOne As Integer = rnd() * 40 (where 40 is the amount of entries in the DB)

Dim DeleteQuery As String = "DELETE * FROM Table LIMIT '" & WhichToDelete & "', '1'"
Dim DeleteCommand As New SqlCommand(DeleteQuery, myConnection)
DeleteCommand.ExecuteNonQuery()

If for example WhichOne = 17, it should execute an SQL query:
DELETE * FROM Table LIMIT '17', '1'
Which imo should delete the 17th entry in the table. Now I get an error on the execution of the SQL command, but what is it that I'm doing wrong

Answer this question

How to randomly delete 1 row from a DB ?

ssfftt
Rather then wrestle with the SQL, just use a DataAdapter to Fill a DataTable, then randomly delete one of the DataTable's DataRows and the use the DataAdapter to Update the deletion back to the database.
asalcedo

Looking at http://msdn2.microsoft.com/en-us/library/ms189835.aspx SQL Books Online DELETE syntax, "LIMIT" is not supported. I believe you will need to use "WHERE ID = @IdToDelete".

Jimmy
TWild
Standard incorrect syntax error message: Incorrect Syntax near "LIMIT".
Mohamed B.
Anybody an idea why it's not working (LIMIT-tag not supported ) and how I can solve this problem
WXS123

Is there a reason you have to delete the row, why not just randomly select a row and then update it And as for the random number of rows just use a For loop to Delete or update one row at a time
Seraphino
I've solved it by using a seperate array in which I loaded the ID's.
Thanks all for the help.
a.s.viswa
Do you have to use the LIMIT i wasn't aware that was supported. If you could tell me a little more about the end goal, perhaps I could be of more use.
Mega_x
Well, I got the idea it indeed isn't supported. :-) It keeps giving a failure around the LIMIT tag.

The idea is that I have a table with multiple rows. Each row contains different randomly choosen values (it takes to much time to explain why all has to be randomly choosen, but it has to) Each row has an autonumbered ID field. In my program I want to randomly delete 25% of the rows, and fill that space with new randomly created rows. Adding the new rows isn't a problem, as it's a simple INSERT statement. The problem is how to delete a row.

My script randomly takes one of the existing rows:

whichtodelete = RND() * amountofrows

Now I want to delete that row. Problem is that I can't use the whichtodelete variable as ID number, cause ID-numbers aren't continious and will increase during the use of the program. For example:I have 10 rows, ID 1 to 10.
If I delete 25% (3 rows), rests rowsID: 1, 3, 4, 5, 7, 9, 10

Then I add 3 new rows, result: ID 1, 3, 4, 5, 7, 9, 10, 11, 12, 13

etc.etc each time you repeat this function.

Now I can do 2 things imo:

- use the limit function, so I can use: DELETE LIMIT whichtodelete, 1

- first read all ID's into an array, do a random function to catch 1 item of that array, and then delete that specific ID, but that needs much more code, that's why I want to use the LIMIT tag.
Lonko
What kind of error do you get Can you post error message here
How to randomly delete 1 row from a DB ?

Answer this question