#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Los Angeles, CA
    Posts
    64
    Rep Power
    11

    SQL Delete multiple records


    Hello folks,

    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
    FROM Favorites
    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...
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    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

    make sense?

    rudy
    http://r937.com/
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Los Angeles, CA
    Posts
    64
    Rep Power
    11
    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.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    DELETE FROM Favorites
    WHERE UserID=4 AND FavID IN (8,9)
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Los Angeles, CA
    Posts
    64
    Rep Power
    11


    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
    FROM Favorites
    WHERE (((Favorites.FavID) In ([@favid])) AND ((Favorites.UserID)=[@userid]));


    But for now I just wrote the SQL statement in ASP and is working.

    Thanks again!

IMN logo majestic logo threadwatch logo seochat tools logo