尝试在ORACLE中运行以下更新查询时ORA-00933

I have the below query which works fine when run in SQL Server. But the same is failing in Oracle db.

我有以下查询在SQL Server中运行时工作正常。但是在Oracle db中也是如此。

Do I need to rewrite the query. Any solutions on how to proceed?

我是否需要重写查询。关于如何进行的任何解决方案?

UPDATE SFFM SET
SFFM.INTSTRTDTE = SCCS.INTSTRTDTE, 
SFFM.INTENDDTE = SCCS.INTENDDTE,    
SFFM.EFFDATE = SCCS.INTSTRTDTE      
FROM SCCS
WHERE SFFM.SECID = 'TEST1'      
AND SFFM.SECID = SCCS.SECID     
AND SFFM.SEQ = SCCS.SEQ 
AND SFFM.AMENDDATE IS NULL

Error is:

Error at Command Line:7 Column:1 Error report: SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

3 个解决方案

#1

You can't write such select. In case of update you need to write subselect if you need to query data from other table:

你不能写这样的选择。如果需要查询其他表中的数据,如果需要更新,则需要编写subselect:

UPDATE SFFM
   SET (SFFM.INTSTRTDTE, SFFM.INTENDDTE, SFFM.EFFDATE) =
       (SELECT SCCS.INTSTRTDTE,
               SCCS.INTENDDTE,
               SCCS.INTSTRTDTE
          FROM SCCS
         WHERE SFFM.SECID = 'TEST1'
           AND SFFM.SECID = SCCS.SECID
           AND SFFM.SEQ = SCCS.SEQ
           AND SFFM.AMENDDATE IS NULL)

#2

Try this:

MERGE INTO SFFM 
USING 
(
  SELECT SCCS.INTSTRTDTE, 
  SCCS.INTENDDTE,    
  SCCS.EFFDATE      
  FROM SCCS
  WHERE SCCS.SECID = 'TEST1'        
  AND SCCS.AMENDDATE IS NULL
) ta ON (ta.SECID = SFFM.SECID AND SFFM.SEQ = ta.SEQ )
WHEN MATCHED THEN UPDATE 
    SET SFFM.INTSTRTDTE = ta.INTSTRTDTE, 
    SFFM.INTENDDTE = ta.INTENDDTE,    
    SFFM.EFFDATE = ta.INTSTRTDTE

#3

ORA-00933: SQL command not properly ended

ORA-00933:SQL命令未正确结束

Because you have a syntax error in your update statement. You cannot do that in Oracle, neither can you directly join the tables in an update statement.

因为更新语句中存在语法错误。您无法在Oracle中执行此操作,也无法直接在更新语句中加入表。

For a verbose solution, you could use MERGE:

对于详细解决方案,您可以使用MERGE:

MERGE INTO SFFM f 
USING(
SELECT INTSTRTDTE, INTENDDTE, INTSTRTDTE, SEQ
FROM SCCS
) c 
ON (f.SECID = c.SECID AND f.SEQ = c.SEQ)
WHEN MATCHED THEN
  UPDATE
  SET f.INTSTRTDTE = c.INTSTRTDTE,
    f.INTENDDTE    = c.INTENDDTE,
    f.EFFDATE      = c.INTSTRTDTE
  WHERE f.SECID    = 'TEST1'
  AND f.AMENDDATE IS NULL;

#1