CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2008-r2
DtotheG
DtotheG

Reputation: 1257

SQL IF Exists or WHERE Exists?

Creating an INSERT query to insert data into an existing table (YPImport) from another table (YPTImport), issue is some of the data in YPTImport is already stored in the YPImport table so if I do a standard import I will create duplicates in the YPImport table.

There is 3 fields that I can use to compare the data against each other to make sure there are no duplicates, CNumber, Location and Date. So if the CNumber, Location and Date match then I dont wan't to insert the row.

I tried using WHERE NOT EXISTS and doing a select with an INNER JOIN on the YPImport table to select only the duplicates but with the WHERE NOT EXISTS it returned 0 rows.

SELECT item,
       CNumber,
       Location,
       Date,
FROM   YPTImport
WHERE  NOT EXISTS (SELECT a.item,
                          a.CNumber,
                          a.Location,
                          a.Date,
                   FROM   YPTImport a
                          INNER JOIN YPImport b
                            ON a.CNumber = b.CNumber
                               AND a.Location = b.Location
                               AND a.Date = b.Date)

Any suggestions?

Upvotes: 2

Views: 202

Answers (1)

Andrey Gordeev
Andrey Gordeev

Reputation: 32459

Use this query:

SELECT
a.item,
a.CNumber,
a.Location,
a.Date
FROM YPTImport a
WHERE NOT EXISTS
(SELECT *
FROM YPImport b 
WHERE a.CNumber = b.CNumber AND a.Location = b.Location AND a.Date= b.Date)

Upvotes: 2

Related Questions