Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Help with Delete command

    I am just learning SQL and need assistance on the sql part.

    I have 2 tables and have following info on these

    Table A : have user_id, application_name

    table B have user_id

    Table B have new and updated list of user id's

    Table A is very huge and to remove old data I used below delete statement but its not working

    delete from A
    where user_id not in (select user_id from B )


    I am not sure what's worng because it is not acutally deleting records that don't exist in table B.

    Your help is very much appreciated.

    Thanks

    --------------------------------------------------------------------------------

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you show us with some example data in all those tables what your start situation is and what you want at the end as result?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does this query return the ones you want to delete?
    Code:
    SELECT *
    FROM   a
     LEFT
      JOIN b
        ON b.user_id = a.user_id
    WHERE  b.user_id IS NULL
    If that shows the correct results then turn it into a DELETE statement:
    Code:
    DELETE
    FROM   a
     LEFT
      JOIN b
        ON b.user_id = a.user_id
    WHERE  b.user_id IS NULL
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2009
    Posts
    2

    Thanks

    Hi George,

    Thank you for the response.

    It did return the values where user_id in table B is null
    but I am not sure if that's the one I am looking at to delete.

    here is more information

    table B have only the User_id's and table A have user_id , application_name.

    I am trying to delete the records such a way that
    if the user_id in table B does not match user_id in table A, then the record should be deleted from table A

    I wrote the sql command as

    Delete from A
    where A.user_id not in (select user_id from B)
    and A.user_id is null;

    but not sure if this would work.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That is what my statement is doing. To illustrate, run the following:
    Code:
    BEGIN TRAN
    
      SELECT *
      FROM   a
      WHERE  user_id NOT IN (SELECT user_id FROM B)
    
      DELETE
      FROM   a
       LEFT
        JOIN b
          ON b.user_id = a.user_id
      WHERE  b.user_id IS NULL
    
      SELECT *
      FROM   a
      WHERE  user_id NOT IN (SELECT user_id FROM B)
    
    ROLLBACK TRAN
    George
    Home | Blog

Posting Permissions

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