更新查询缺少右括号 [英] Update query missing right parenthesis

问题描述

UPDATE CONVERTED T1 
   SET PARENTID = (SELECT ID FROM 
                  CONVERTED T2 WHERE T2.ID < T1.ID 
                  AND T1.PREVOBJNUM = T2.OBID 
                  AND ROWNUM = 1 
                  ORDER BY ID DESC)

给我以下错误

L Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

如果我删除ORDER BY，它会起作用.知道如何解决吗?

If I remove the ORDER BY it works. Any idea how to fix it?

推荐答案

该语句中不能包含orderby.像这样修改它来绕开限制.另外，如果您想返回正确的记录，我建议将rownum移到第二个选择之外.在内部选择中，Rownum将不正确，因为它是在orderby之前应用的.如果您按内部选择中的rownum进行过滤，则是否选择最大ID(它就是您想要的最大ID)会成败.

You can't have an orderby in that statement. Modify it like so to get around the restriction. Also, if you want to return the correct record, I would suggest moving the rownum outside to the second select. Rownum will be incorrect in the inner select since it's applied before the orderby. If you filter by the rownum in the inner select, it's hit or miss whether or not you'll select the max ID, which is what it looks like you want.

UPDATE CONVERTED T1 
   SET PARENTID = (
    SELECT ID FROM (
        SELECT ID FROM 
        CONVERTED T2 WHERE T2.ID < T1.ID 
        AND T1.PREVOBJNUM = T2.OBID 
        ORDER BY ID DESC)
    WHERE ROWNUM = 1 
    )

这篇关于更新查询缺少右括号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！