CODEWITHSUNDEEP
sqlsql-serverif-statement
Chanter
Chanter

Reputation: 109

IF NOT EXISTS statement not working correctly after 1st execution

I am trying to insert data into a table feed.author from another table feed.tempXML. The First time I execute this statement it works perfectly fine, but when i add data in feed.tempXML and run it again the new authors are not being added into the feed.author table. Does the IF statement need to change ?

Code: 

IF NOT EXISTS(SELECT  DISTINCT t.author FROM feed.tempXML t 
    JOIN feed.author a ON a.author = t.author)
        INSERT INTO feed.author(author)
        SELECT  DISTINCT author
        FROM feed.tempXML
        WHERE author  IS NOT NULL

Upvotes: 0

Views: 103

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

Problem in your query is, In IF condition you checking for the record existence but while inserting you are not doing it.

It should be done like this 

INSERT INTO feed.author
            (author)
SELECT DISTINCT author
FROM   feed.tempXML t
WHERE  NOT EXISTS (SELECT 1
                   FROM   feed.author a
                   WHERE  a.author = t.author)
       AND t.author IS NOT NULL

Upvotes: 4

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

If I understand your problem right, why not to use EXCEPT?

INSERT INTO feed.author (author)
SELECT author
FROM feed.tempXML
WHERE author IS NOT NULL
EXCEPT
SELECT author
FROM feed.author;

This will insert users from feed.tempXML that do not yet exist in feed.author

Also, if remember that 'author' from 'feed.tempXML' table can have empty strings (in a theory, maybe in your case not) so it is wise to replace IS NOT NULL with > ''. So the query becomes:

INSERT INTO feed.author (author)
SELECT author
FROM feed.tempXML
WHERE author > ''
EXCEPT
SELECT author
FROM feed.author;

Upvotes: 0

Related Questions