关于SQL两表关联的Insert有关问题

关于SQL两表关联的Insert问题?

SQL code

条件：select distinct EcrNo,SendData as ECORELEASEDATE from RES_ECO_PASS where EcrNo in (select ECRNO from RES_ECR_TYPE)EcrNo                                              ECORELEASEDATE-------------------------------------------------- -----------------------ECR-019625                                         2008-12-12 00:00:00ECR-019631                                         2008-12-12 00:00:00ECR-019638                                         2008-12-12 00:00:00ECR-019652                                         2008-12-12 00:00:00ECR-019653                                         2008-12-12 00:00:00ECR-019655                                         2008-12-12 00:00:00ECR-019656                                         2008-12-12 00:00:00ECR-019662                                         2008-12-12 00:00:00ECR-019663                                         2008-12-12 00:00:00ECR-019664                                         2008-12-12 00:00:00ECR-019667                                         2008-12-12 00:00:00ECR-019668                                         2008-12-12 00:00:00ECR-019669                                         2008-12-12 00:00:00ECR-019672                                         2008-12-12 00:00:00ECR-019678                                         2008-12-12 00:00:00ECR-019679                                         2008-12-12 00:00:00ECR-019682                                         2008-12-12 00:00:00ECR-019683                                         2008-12-12 00:00:00ECR-019684                                         2008-12-12 00:00:00ECR-019686                                         2008-12-12 00:00:00(20 個資料列受到影響)关联插入以上20笔记录：insert into RES_ECR_TYPE(ECONO,ECORELEASEDATE) select distinct a.EcrNo,a.SendData as ECORELEASEDATE from RES_ECO_PASS a left join RES_ECR_TYPE b on a.EcrNo=b.ECONO and a.SendData=b.ECORELEASEDATE where a.EcrNo in (select b.ECRNO from RES_ECR_TYPE)为何Insert为0笔记录，显示：(0 個資料列受到影響),是不是SQL有问题？？？请大侠指点！！！

[解决办法]
楼主的第二条语句中多了一个筛选条件a.SendData=b.ECORELEASEDATE， 导致符合筛选条件的结果行数为0.
其实那个join语句是完全没必要的，第一个筛选条件on a.EcrNo=b.ECONO也与后面的where子句作用重复。

建议回归为第一个句子的形式：

SQL code

insert into RES_ECR_TYPE(ECONO,ECORELEASEDATE)select distinct EcrNo,SendData as ECORELEASEDATE from RES_ECO_PASS where EcrNo in (select ECRNO from RES_ECR_TYPE)
[解决办法]
SQL code
    update a set a.ECONO=b.ECONO    from RES_ECR_TYPE a,    (    select distinct EcrNo,ECONO,ECOCC,SendData as ECORELEASEDATE     from RES_ECO_PASS     where EcrNo in (select ECRNO from RES_ECR_TYPE)    )b    where a.ECRNO=b.EcrNo        --update<ECOCC>    update a set a.ECROCC=b.ECOCC    from RES_ECR_TYPE a,    (    select distinct EcrNo,ECONO,ECOCC,SendData as ECORELEASEDATE     from RES_ECO_PASS     where EcrNo in (select ECRNO from RES_ECR_TYPE)    )b     where a.ECRNO=b.EcrNo        --update<ECORELEASEDATE>    update a set a.ECORELEASEDATE=b.ECORELEASEDATE    from RES_ECR_TYPE a,    (    select distinct EcrNo,ECONO,ECOCC,SendData as ECORELEASEDATE    from RES_ECO_PASS    where EcrNo in (select ECRNO from RES_ECR_TYPE)    )b    where a.ECRNO=b.EcrNo 

 

查看更多 下一篇