插入“report_temp”行哪些不在“报告”中？

发布于 2024-09-08 19:38:12 字数 750 浏览 3 评论 0原文

我有两个表：

report (reportID, VendorName, VendorID, MfgDate, PurchaseDate, etc.,)

在 report 表中，reportID 是主键。

report_temp 与 report 具有相同的列集，但没有任何限制。

我必须将report_temp 中的行插入到report，其中reportID 不相同。我已经写过，因为

  INSERT INTO report(reportID, VendorName, VendorID, MfgDate, PurchaseDate,...) 
      NOT (SELECT reportID, VendorName, VendorID, MfgDate, PurchaseDate,... 
           FROM report INNER JOIN report_temp USING (reportID, VendorName, 
                VendorID, MfgDate,PurchaseDate,...))

我也尝试过在 USING 子句中仅使用 reportID ，但我无法得到它...... 如果您尝试过类似的事情，请与我分享..

原文

I've two tables :

report (reportID, VendorName, VendorID, MfgDate, PurchaseDate, etc.,)

In report table reportID is primary key.

report_temp has the same set of columns as report, but not any constraints.

I've to insert the rows from report_temp to report where the reportID is not the same.
I've written as

  INSERT INTO report(reportID, VendorName, VendorID, MfgDate, PurchaseDate,...) 
      NOT (SELECT reportID, VendorName, VendorID, MfgDate, PurchaseDate,... 
           FROM report INNER JOIN report_temp USING (reportID, VendorName, 
                VendorID, MfgDate,PurchaseDate,...))

I've also tried with just reportID within USING clause, but I can't get it...
If you tried anything like this share with me..

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ζ澈沫 2024-09-15 19:38:12

这将为您提供两个表中都存在的reportID集：

SELECT report_temp.reportID FROM report_temp, report WHERE report_temp.reportID=report.reportID

因此，这将为您提供要插入的行：

SELECT * FROM report_temp WHERE reportID NOT IN (SELECT report_temp.reportID FROM report_temp, report WHERE report_temp.reportID=report.reportID)

然后只需将其放入report表中即可：

INSERT INTO report SELECT * FROM report_temp WHERE reportID NOT IN (SELECT report_temp.reportID FROM report_temp, report WHERE report_temp.reportID=report.reportID)

This will give you the set of reportIDs existing in both tables:

SELECT report_temp.reportID FROM report_temp, report WHERE report_temp.reportID=report.reportID

So this will give you the rows to insert:

SELECT * FROM report_temp WHERE reportID NOT IN (SELECT report_temp.reportID FROM report_temp, report WHERE report_temp.reportID=report.reportID)

Then just drop that into the report table:

INSERT INTO report SELECT * FROM report_temp WHERE reportID NOT IN (SELECT report_temp.reportID FROM report_temp, report WHERE report_temp.reportID=report.reportID)

回复收藏 0 原文

~没有更多了~