我正在尝试将数据从另一个tablefeed.tempXML插入到tablefeed.author中。我第一次执行此语句时,它工作得很好,但是当我在feed.tempXML中添加数据并再次运行它时,新的作者不会添加到feed.author表中。IF语句需要更改吗?
法典:
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
查询中的问题是,在这种情况下IF
您检查记录是否存在,但在插入时您没有这样做。
应该这样做
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
如果我理解你的问题,为什么不使用EXCEPT
?
INSERT INTO feed.author (author)
SELECT author
FROM feed.tempXML
WHERE author IS NOT NULL
EXCEPT
SELECT author
FROM feed.author;
这将插入feed.author
中尚不存在的feed.tempXML
中的用户
另外,请记住"feed.tempXML"表中的"作者"可以有空字符串(在理论上,也许在您的情况下没有(,因此明智的做法是将IS NOT NULL
替换为> ''
。因此,查询变为:
INSERT INTO feed.author (author)
SELECT author
FROM feed.tempXML
WHERE author > ''
EXCEPT
SELECT author
FROM feed.author;