i具有以下查询,在SQL Server中运行时可以正常工作。但是在Oracle DB中也失败了。
我需要重写查询。有关如何进行的任何解决方案?
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 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:
您无法编写这样的选择。如果您需要从其他表查询数据,则需要编写子选择:
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)
尝试以下:
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
ora-00933:sql命令未正确结束
因为您在更新语句中有语法错误。您不能在Oracle中执行此操作,您也不能直接加入表格中的表格。
对于详细解决方案,您可以使用 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;