Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: syntax assistance delete statement

    Ok folks...try to hold back the laughter.

    I've been away from MySql for a while and now find it neccesary to jump back in...so to that end...

    DELETE FROM mlsdb.virtualdb LEFT JOIN mlsdb.mlstable ON (mlsdb.virtualdb.mlsnum = mlsdb.virtualdb.mlsnum)WHERE mlsdb.mlstable.ListID <> mlsdb.virtualdb.mlsnum

    I have a db with 19 tables. I need to delete the records from the table "virtualdb" that do not have corresponding ID numbers in the table "mlstable"

    I came up with the above...which obviously dosn't work, lol. any help would be appreciated.

    TIA
    Last edited by glyndower; 01-31-04 at 05:47.

  2. #2
    Join Date
    Nov 2003
    Posts
    91
    try this,

    DELETE mlsdb.virtualdb FROM mlsdb.virtualdb, mlsdb.mlstable
    WHERE mlsdb.mlstable.ListID = mlsdb.virtualdb.mlsnum
    AND mlsdb.mlstable.ListID IS NULL

    this doesn't have a shot at working unless you are running
    MySQL 4.0.

  3. #3
    Join Date
    Jan 2004
    Posts
    4

    nope

    MySQL 3.23.54

    tried it anyway, but you were right...didnt work. Thanks for the suggestion though!

  4. #4
    Join Date
    Nov 2003
    Posts
    91
    Then you can make a list of IDs you want to delete,

    SELECT mlsdb.virtualdb.mlsnum
    FROM mlsdb.virtualdb LEFT JOIN mlsdb.mlstable
    ON mlsdb.mlstable.ListID = mlsdb.virtualdb.mlsnum
    WHERE mlsdb.mlstable.ListID IS NULL

    and delete 'em,

    DELETE FROM mlsdb.virtualdb WHERE mlsdb.virtualdb.mlsnum
    IN ( <your_list> )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •