MYSQL从一个表插入另一个表但出现错误: subquery returns more than 1 row

Question

我在 MYSQL 数据库中有两个表 (cssp_release_info,det_release_info)，我想将一些记录从 cssp_release_info 复制到 det_release_info 以及以下内容这是我的查询。

    INSERT INTO det_release_info (case_urn,acc_status,reason,remarks,userid,department,ministry,registerdate,updated,released,DATE,expected_date,actual_date,is_executed,exec_details,other_reduction,classified)
SELECT t2.case_urn,t2.acc_status,t2.reason,t2.remarks,t2.userid,t2.department,t2.ministry,t2.registerdate,t2.updated,t2.released,t2.date,t2.expected_date,t2.actual_date,t2.is_executed,t2.exec_details,t2.other_reduction,t2.classified FROM cssp_release_info AS t2 LEFT JOIN det_release_info AS t3 ON t3.case_urn=t2.case_urn WHERE t2.acc_status=42 AND t3.registerdate IS NULL

但是当我运行查询时，出现错误错误代码:1242
子查询返回多于 1 行


Answer 1

到目前为止，您的查询很好，但我猜您想使用OUTER JOIN，您需要在条件中让AND t3.registerdate IS NULL，否则， mysql引擎会让它成为INNER JOIN

INSERT INTO det_release_info 
            (case_urn, 
             acc_status, 
             reason, 
             remarks, 
             userid, 
             department, 
             ministry, 
             registerdate, 
             updated, 
             released, 
             date, 
             expected_date, 
             actual_date, 
             is_executed, 
             exec_details, 
             other_reduction, 
             classified) 
SELECT t2.case_urn, 
       t2.acc_status, 
       t2.reason, 
       t2.remarks, 
       t2.userid, 
       t2.department, 
       t2.ministry, 
       t2.registerdate, 
       t2.updated, 
       t2.released, 
       t2.date, 
       t2.expected_date, 
       t2.actual_date, 
       t2.is_executed, 
       t2.exec_details, 
       t2.other_reduction, 
       t2.classified 
FROM   cssp_release_info AS t2 
WHERE  t2.acc_status = 42 AND t2.case_urn NOT IN (
    SELECT case_urn 
    FROM det_release_info
)

这是插入到选择中的示例。

sqlfiddle

编辑

您的查询与NOT IN相同，所以请尝试一下。

INSERT INTO det_release_info 
            (case_urn, 
             acc_status, 
             reason, 
             remarks, 
             userid, 
             department, 
             ministry, 
             registerdate, 
             updated, 
             released, 
             date, 
             expected_date, 
             actual_date, 
             is_executed, 
             exec_details, 
             other_reduction, 
             classified) 
SELECT t2.case_urn, 
       t2.acc_status, 
       t2.reason, 
       t2.remarks, 
       t2.userid, 
       t2.department, 
       t2.ministry, 
       t2.registerdate, 
       t2.updated, 
       t2.released, 
       t2.date, 
       t2.expected_date, 
       t2.actual_date, 
       t2.is_executed, 
       t2.exec_details, 
       t2.other_reduction, 
       t2.classified 
FROM   cssp_release_info AS t2 
WHERE  t2.acc_status = 42 AND t2.case_urn NOT IN (
    SELECT case_urn 
    FROM det_release_info
)