HANA中的IF EXISTS语句替代

问题描述：

我想查找如果存在"，但无法了解"sap hana sql"是否存在如果存在".我是否被创建sql-command错误.

I want looked for "if exists", but sap hana sql was not understand for "if exists". whether i was created sql-command wrong.

如果记录的"Y"存在于ZZZ_Exercise_3的表中，否则记录的"N"不存在于ZZZ_Exercise_3的表中.如果旧记录为"N"，则应删除该记录.

If 'Y' for the record exists in table from ZZZ_Exercise_3, else 'N' for the record not exists in table from ZZZ_Exercise_3. If old record for 'N' then this record delete should.

 CREATE COLUMN TABLE ers.ZZZ_EXERCISE_5(
    ID INT NOT NULL PRIMARY KEY generated by default as IDENTITY,
    Orig_ID INT,
    COMPANY nvarchar(251),
    VALID char(1)
);

COMMIT;

IF EXISTS(
    INSERT INTO ZZZ_EXERCISE_5(Orig_ID, COMPANY, VALID)
    SELECT ID, COMPANY, 'Y' FROM ZZZ_EXERCISE_3
)
ELSE(
    INSERT INTO ZZZ_EXERCISE_5(Orig_ID, COMPANY, VALID)
    SELECT ID, COMPANY, 'N' FROM ZZZ_EXERCISE_3
)

COMMIT;

UPDATE ZZZ_EXERCISE_3
SET COMPANY = (SELECT COMPANY FROM ERS.ZZZ_EXERCISE_5)
WHERE NOT EXISTS (SELECT COMPANY FROM ZZZ_EXERCISE_5 WHERE ZZZ_EXERCISE_5.VALID = 'N')

COMMIT;

DELETE FROM ZZZ_EXERCISE_3
WHERE ERS.ZZZ_EXERCISE_5.VALID = 'N'

SELECT * FROM ZZZ_EXERCISE_3
WHERE NOT EXISTS (SELECT COMPANY FROM ZZZ_EXERCISE_5 WHERE ZZZ_EXERCISE_5.ID = ZZZ_EXERCISE_3.ID)

答

不确定哪种SQL方言会接受您的语句，但是IF-THEN-ELSE通常是过程扩展的一部分，例如PL/SQL，T-SQL或SQLScript在SAP HANA中.

Not sure which SQL dialect would accept your statement, but IF-THEN-ELSE is typically part of the procedural extension like PL/SQL, T-SQL or SQLScript in SAP HANA.

如果您要详细说明要实现的目标(而不是仅仅发布一些不起作用的非标准语法)，则在SAP HANA中可能会有选择来实现.

If you explain more about what you want to achieve (instead of just posting some non-standard syntax that does not work), there are likely options to do it in SAP HANA.

如果记录的"Y"存在于ZZZ_Exercise_3的表中，否则记录的"N"不存在于ZZZ_Exercise_3的表中.如果旧记录为"N"，则应删除该记录.

If 'Y' for the record exists in table from ZZZ_Exercise_3, else 'N' for the record not exists in table from ZZZ_Exercise_3. If old record for 'N' then this record delete should.

根据修改后的说明，我的理解是您想要

Based on the revised description, my understanding is that you want

对于ZZZ_EXERCISE_5中的每个记录: 检查它是否存在于ZZZ_EXERCISE_3中，如果存在，请将ZZZ_EXERCISE_5中的VALID字段设置为Y.
如果记录存在于ZZZ_EXERCISE_3中但不存在于ZZZ_EXERCISE_5中，则将该记录插入到ZZZ_EXERCISE_5中.
如果记录不在ZZZ_EXERCISE_3中而是在ZZZ_EXERCISE_5中，则将VALID设置为N.
此后，您要删除ZZZ_EXERCISE_3中匹配记录具有VALID = N

ZZZ_EXERCISE_3

For every record in ZZZ_EXERCISE_5: check if it exists in ZZZ_EXERCISE_3 and if so, set the VALID field in ZZZ_EXERCISE_5 to Y.
If the records exist in ZZZ_EXERCISE_3 but not in ZZZ_EXERCISE_5, insert that record into ZZZ_EXERCISE_5.
If the records don't exist in ZZZ_EXERCISE_3 but in ZZZ_EXERCISE_5, set VALID to N.
After that, you want to delete all records from ZZZ_EXERCISE_3 for which a matching record in ZZZ_EXERCISE_5 has VALID = N

对于步骤1，您想查看

第2步是一个简单的步骤，所以让我们开始吧:

Step 2 is the easier one, so let's start with that:

   DELETE FROM ZZZ_EXERCISE_3 
   WHERE ID IN (SELECT DISTINCT ID 
                FROM ZZZ_EXERCISE_5 
                WHERE VALID ='N');

第1步要复杂一些，因为它必须将UPDATE和INSERTS结合起来.为此，SAP HANA提供了MERGE命令(也称为REPLACE). MERGE |替换文档

Step 1 is a bit more complicated as it shall combine UPDATEs and INSERTS. For this, SAP HANA provides the MERGE command (also called REPLACE). MERGE|REPLACE DOCU

对于SAP HANA 2，还有一个新命令MERGE INTO，它与其他DBMS中常用的MERGE命令更接近. 合并到文档中.

With SAP HANA 2, there also a new command MERGE INTO that is closer to the commonly used MERGE command in other DBMS. MERGE INTO DOCU.

HANA中的IF EXISTS语句替代

相关推荐