如何根据特定条件将数据从一个表拉到另一个表？(How to pull data from one table to another table based on certain condition?)

如何根据特定条件将数据从一个表拉到另一个表？(How to pull data from one table to another table based on certain condition?)

我有两张桌子。 表1名为RAW ，表2名为REPORT 。 请看下面的截图。 这两个表都与列名称REPORT.REQUESTID有关系，但唯一的区别是 - REPORT.REQUESTID是REPORT.REQUESTID的DISTINCT ， REPORT表的其余字段是空白的。

我正在寻找Query来填充REPORT表中的数据但我的条件是

在NEW列中INSERT数据

REPORT.REQUESTID = RAW.REQUESTID AND RAW.LASTSTATUS= "NEW"

当上述条件为真时， REPORT.NEW应该具有来自RAW.LASTUPDATEON的值作为其RAW.LASTUPDATEON 。 结果将类似于我手动填充的屏幕截图。

表格和结果

I've two tables. Table one is named RAW and table two is named REPORT. Please see below screenshots. Both the tables are in Relationship with Column name REQUESTID, however the only difference is - REPORT.REQUESTID is DISTINCT of RAW.REQUESTID and rest of the fields of REPORT table are blank.

I'm looking for Query to fill data in REPORT table but my conditions are

To INSERT Data in NEW Column

REPORT.REQUESTID = RAW.REQUESTID AND RAW.LASTSTATUS= "NEW"

When above condition is true, REPORT.NEW should have the value from RAW.LASTUPDATEON for their REQUESTID. Result will look like in the screenshot which I've manually filled.

Tables and Results

最满意答案

查询将是这样的

INSERT INTO REPORT (REQUESTID, NEW) SELECT REQUESTID, LASTUPDATEDON FROM RAW WHERE LASTSTATUS = "NEW"

插入表中不存在的值

INSERT INTO REPORT (REQUESTID, NEW) SELECT REQUESTID, LASTUPDATEDON FROM RAW WHERE LASTSTATUS = "NEW" AND REQUESTID NOT IN (SELECT REQUESTID FROM REPORT)

I found it myself. Here's the query I was looking for.

UPDATE MAIN INNER JOIN RAW ON MAIN.REQUESTID=RAW.REQUESTID SET MAIN.NEW = [RAW].[LASTUPDATEON] WHERE RAW.LASTSTATUS='NEW';