Delete records using Minus command in sql

Question

Delete records using Minus command in sql

vijaykumar.s

SSC Veteran

Points: 222
More actions
October 27, 2017 at 12:54 pm

#340993

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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

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);

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.

vijaykumar.s SSC Veteran Points: 222 More actions · Answer 5

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 ..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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?

vijaykumar.s SSC Veteran Points: 222 More actions · Answer 7

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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?

vijaykumar.s SSC Veteran Points: 222 More actions · Answer 9

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

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?

vijaykumar.s SSC Veteran Points: 222 More actions · Answer 11

vijaykumar.s

SSC Veteran

Points: 222

October 27, 2017 at 2:35 pm

#1965621

that's the reason I used MINUS..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

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.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 13

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');

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 14

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.