October 16th, 2003, 09:15 PM
SQL Delete multiple records
I trying to delete multiple records at once from a Table called 'Favorites' with 2 criterias but it does not seem to work. Only one record gets deleted.
I have a form where users can select from a check box which records to delete. I want to delete ALL the records that the user has selected.
This is the Stored procedure I am using in Access 2000.
DELETE Favorites.UserID, Favorites.FavID
WHERE (((Favorites.UserID)=[@usserid]) AND ((Favorites.FavID)=[@favid]));
Can anyone help me correct this statement to delete all the FavID items for this user. I am thinking some kind of loop is needed but that would make the DB very busy...
October 16th, 2003, 10:35 PM
DELETE will only delete rows
it appears that you want to delete column values?
note that DELETE does not take a column list
your statement should be
DELETE FROM tablename WHERE...
the WHERE clause will indicate which rows
October 16th, 2003, 10:53 PM
FavoriteID UserID FavID
1 2 6
2 4 8
3 4 9
4 6 5
Above is my table, FavoriteID is auto number.
I am trying to delete rows 2 & 3 in one statement. This userID, 4 has 2 items in the Favorites and he has selected to delete both items.
Hope this is more clear than my earlier post.
Rudy, I am not sure what you mean by delete column values
Thanks for your help.
October 16th, 2003, 11:14 PM
DELETE FROM Favorites
WHERE UserID=4 AND FavID IN (8,9)
October 16th, 2003, 11:44 PM
Thanks for your help Rudy!
Got it working, however I can't get the Stored procedure in Access to work:
DELETE Favorites.FavID, Favorites.UserID
WHERE (((Favorites.FavID) In ([@favid])) AND ((Favorites.UserID)=[@userid]));
But for now I just wrote the SQL statement in ASP and is working.