Delete records using Minus command in sql

  • I have two tables and I want to delete records from one table for which there is no matching records in second table. Now the question is How to delete records using MINUS SQL Statement? Please explain with example

    DELETE from Table1

    (select COL1 from Table1
    MINUS
    select Col2 from Table 2)

    Table1 will have extra records compared to table2 so I want to delete extra records from table 1

    Vijay

  • vijaykumar.s - Friday, October 27, 2017 12:54 PM

    I have two tables and I want to delete records from one table for which there is no matching records in second table. Now the question is How to delete records using MINUS SQL Statement? Please explain with example

    DELETE from Table1

    (select COL1 from Table1
    MINUS
    select Col2 from Table 2)

    Table1 will have extra records compared to table2 so I want to delete extra records from table 1

    Vijay

    Unfortunately, SQL Server doesn't have a MINUS statement.
    Is there a common key value between the two tables?

  • vijaykumar.s - Friday, October 27, 2017 12:54 PM

    DELETE from Table1 

    (select COL1 from Table1
    MINUS
    select Col2 from Table 2)

    MINUS is EXCEPT operator in T-SQL.  However it may be easier to think of the problem this way:
    DELETE FROM Table1 t1
    WHERE NOT EXISTS
      (SELECT NULL FROM Table2 t2 WHERE t2.Col2 = t1.Col1);

  • Lynn Pettis - Friday, October 27, 2017 1:19 PM

    vijaykumar.s - Friday, October 27, 2017 12:54 PM

    I have two tables and I want to delete records from one table for which there is no matching records in second table. Now the question is How to delete records using MINUS SQL Statement? Please explain with example

    DELETE from Table1

    (select COL1 from Table1
    MINUS
    select Col2 from Table 2)

    Table1 will have extra records compared to table2 so I want to delete extra records from table 1

    Vijay

    Unfortunately, SQL Server doesn't have a MINUS statement.
    Is there a common key value between the two tables?

    It has a MINUS operator, it's called EXCEPT (which I believe is the standard).
    However, this wouldn't work the way it's written (at least not in SQL Server).
    The most common approach would be to use Exists:

    DELETE t1
    from #Table1 t1
    WHERE EXISTS
        (select * from #Table2 t2
        WHERE t1.Col1 = t2.Col2);

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Friday, October 27, 2017 1:33 PM

    Lynn Pettis - Friday, October 27, 2017 1:19 PM

    vijaykumar.s - Friday, October 27, 2017 12:54 PM

    I have two tables and I want to delete records from one table for which there is no matching records in second table. Now the question is How to delete records using MINUS SQL Statement? Please explain with example

    DELETE from Table1

    (select COL1 from Table1
    MINUS
    select Col2 from Table 2)

    Table1 will have extra records compared to table2 so I want to delete extra records from table 1

    Vijay

    Unfortunately, SQL Server doesn't have a MINUS statement.
    Is there a common key value between the two tables?

    It has a MINUS operator, it's called EXCEPT (which I believe is the standard).
    However, this wouldn't work the way it's written (at least not in SQL Server).
    The most common approach would be to use Exists:

    DELETE t1
    from #Table1 t1
    WHERE EXISTS
        (select * from #Table2 t2
        WHERE t1.Col1 = t2.Col2);

    Chris has it right with the NOT EXISTS.  OP wants to delete data in table1 that does not exist in table2.

  • I tried it did not work
    my script looks like this 

    (select col1 from Table1
    MINUS
    select concat('A_',col1) from Table2 where 
    Col2 = '748' and
    Col3 = 'D' and
    Col4 = 'Account')

    and I want to delete the out put records from above command ..

  • vijaykumar.s - Friday, October 27, 2017 2:12 PM

    I tried it did not work
    my script looks like this 

    (select col1 from Table1
    MINUS
    select concat('A_',col1) from Table2 where 
    Col2 = '748' and
    Col3 = 'D' and
    Col4 = 'Account')

    and I want to delete the out put records from above command ..

    Are you trying to delete data from Table1 that doesn't exist in Table2 or are you just trying to see what is in table1 that doesn't exist in table2?

  • I'm just trying to delete data from Table1 which does not exist in Table2...
    that's it..

  • vijaykumar.s - Friday, October 27, 2017 2:23 PM

    I'm just trying to delete data from Table1 which does not exist in Table2...
    that's it..

    First, I can't find MINUS in any MS SQL Server documentation.  This makes me wonder if you are using Oracle.
    Second, and I asked this before, what column or columns provide a one to one relationship between the data in table1 and table2?

  • there is no one to one mapping between these 2 tables..

  • vijaykumar.s - Friday, October 27, 2017 2:28 PM

    there is no one to one mapping between these 2 tables..

    Then how are you going to determine what exists in one table versus another?

  • that's the reason I used MINUS..

  • vijaykumar.s - Friday, October 27, 2017 2:35 PM

    that's the reason I used MINUS..

    One, as I said, I can't find MINUS in any SQL Server documentation.  We have EXCEPT.  If you are using MINUS are you running Oracle?
    Also, there has to be SOMETHING you are comparing between the two tables to determine what exists or doesn't exist between them.

  • given the new listing for Vijay's query, maybe something like:
    DELETE FROM Table1 t1
    WHERE NOT EXISTS
      (SELECT NULL FROM Table2 t2
       WHERE t1.col1 = concat('A_',t2.col1)
       AND t2.Col2 = '748'
       AND t2.Col3 = 'D' and
       AND t2.Col4 = 'Account');

  • vijaykumar.s - Friday, October 27, 2017 12:54 PM

    MINUS is Oracle dialect. ANSI/ISO Standard SQL, DB2 and T-SQL  use EXCEPT [ALL]

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply