如何插入两行? [英] How are two rows inserted?

问题描述

当我运行以下代码时，它将插入与'07_%'相似的两行，由于创建的日期为'07-AUG-15'，因此它们最初在tlp中不存在.

When I run the following code, it inserts two rows that are like '07_%', that do not exist initially in tlp because date created is '07-AUG-15'.

最初在GLOB中我有600个like '07_%'行，那么如何插入这些行?

Initially in GLOB I have 600 rows which are like '07_%', so how are these rows inserted??

  INSERT INTO TLP
            (LASTNAME, FIRSTNAME, EMAIL1, USERID) 
SELECT NAME, GIVEN_NAME, MAIL, LOGIN
FROM   gLOB 
WHERE (( ( NOT EXISTS (SELECT EMAIL1 
                     FROM   TLP 
                     WHERE  GLOB.mail = TLP.email1 ) 
         AND GLOB.mail IS NOT NULL )
         AND GLOB.COUNTRY='GERMANY' )
        OR (( NOT EXISTS (SELECT userid
                         FROM   TLP
                         WHERE  GLOB.LOGIN = TLP.userid ) 
              )
  AND GLOB.COUNTRY='Germany'))
  and ( exists (select FIRSTNAME
                  from TLP
                  where (gLOB.name not like '07_%' AND 
                 GLOB.name not like 'TR_%' AND
                 GLOB.name not like 'ST_%' AND

                    GLOB.name is not null))

                 ));

推荐答案

我相信您正在尝试过滤出07-AUG-15之类的值，但是您喜欢的模式表示为07_%.应该是

I believe you are trying to filter out values such 07-AUG-15 but your like pattern says 07_%. It should be

gal.name not like '07-%'

此外，您在下面指出的最后一部分没有任何意义；相反，您应该在主查询WHERE子句中包括这些条件.

Also, your last part as pointed below makes no sense; rather you should include those condition in main query WHERE clause.

and ( exists (select FIRSTNAME
                  from TCPD_PERSONEL_COPY
                  where (gal.name not like '07_%' AND 
                 gal.name not like 'TR_%' AND
                 gal.name not like 'ST_%' AND
                  gal.name not like 'KB_%'  AND
                   gal.name not like 'HS_%'  AND
                    gal.name is not null))

您的SELECT应该看起来像

SELECT SUBSTR(NAME, 1, 50),SUBSTR( GIVEN_NAME, 1, 50), DEPARTMENT, PHONE, SUBSTR(MAIL, 1, 100), SUBSTR(LOGIN, 1, 255), SYSDATE 
FROM   gal 
WHERE ( NOT EXISTS (SELECT EMAIL1 
                     FROM   TCPD_PERSONEL_COPY 
                     WHERE  GAL.mail = TCPD_PERSONEL_COPY.email1)

        OR NOT EXISTS (SELECT userid
                         FROM   TCPD_PERSONEL_COPY 
                         WHERE  gAL.LOGIN = TCPD_PERSONEL_COPY.userid))
AND GAL.mail IS NOT NULL AND GAL.COUNTRY='GERMANY'
AND (            gal.name not like '07_%' OR 
                 gal.name not like 'TR_%' OR
                 gal.name not like 'ST_%' OR
                  gal.name not like 'KB_%'  OR
                   gal.name not like 'HS_%'  OR
                    gal.name is not null);

这篇关于如何插入两行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！