none
Delete records using Minus command in sql... RRS feed

  • Question

  • 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

    Friday, October 27, 2017 6:48 PM

All replies

  • use except,

    select COL1 from Table1
    Excpet
    select Col2 from Table 2


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by pituachMVP Sunday, October 29, 2017 7:57 AM
    Friday, October 27, 2017 7:19 PM
  • delete t1
       from table1 t1
        left join table2 t2
          on t1.COL1 = t2.COL2
               where t2.COL2 IS NULL;
    


    Please mark as answered, If you feel happy with this answer.


    Friday, October 27, 2017 7:24 PM
  • tried Expect function but it gave 00933. 00000 -  "SQL command not properly ended" to be more precise my script is

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

    Friday, October 27, 2017 7:45 PM
  • tried Expect function but it gave 00933. 00000 -  "SQL command not properly ended" to be more precise my script is

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

    is this T-SQL script ?

     


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, October 27, 2017 8:05 PM
  • no.. its a normal sql script ...
    Friday, October 27, 2017 8:10 PM
  • no.. its a normal sql script ...

    I should have been more clearer , what is the product used to run this script? 

    T-SQL is programming language for SQL Server product , from your script it looks like doesn't belong to SQL Server. In that case , this may not be right forum for this question.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, October 27, 2017 8:16 PM
  • no.. its a normal sql script ...

    The error message suggests an Oracle script. This is a Microsoft SQL Server forum. I suggest you post Oracle questions to https://stackoverflow.com/questions/tagged/oracle with an oracle tag.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, October 27, 2017 8:16 PM
    • Proposed as answer by pituachMVP Sunday, October 29, 2017 7:57 AM
    Friday, October 27, 2017 8:16 PM
  • Oracle Sql Developer Version 4.1.1.19

    I just want the difference for

    select col1 from Table1 and

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

    and I want to delete difference in table 1

    Friday, October 27, 2017 8:20 PM
  • Oracle Sql Developer Version 4.1.1.19

    Then ask this in an Oracle forum, as Dan already wrote.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, October 28, 2017 1:42 AM
  • 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

    Good day

    Please check the name of the forum!

    Getting started with SQL Server

    Moreover, please check the name of the company that own the website :-)
    The URL start with: https://social.msdn.microsoft.com/...

    Do you see the issue with your question?

    * Please close the thread by marking one or more of the responses you got, and as others mentioned you should ask the question in Oracle forum (Oracle is not related to Microsoft and it is not SQL Server)

    Regards,


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, October 29, 2017 7:57 AM