我使用的資料庫是ORACLE 8i 我在TQuery裡寫以下的SQL程式， 但一直出現"SQL的結束命令有問題"， 請問哪裡寫錯了 update RunCard_list set RunCard_list.rnl_Mac_ID = A.Mac_ID, RunCard_list.rnl_Clk_No = A.Clk_No, RunCard_list.rnl_B_Date = A.TxBDateTime, RunCard_list.rnl_E_Date = A.TxEDateTime, RunCard_list.rnl_ClassType = A.ClassType, RunCard_list.rnl_IpqcQty = A.IpqcQty, RunCard_list.rnl_PassQty = A.PassQty, RunCard_list.rnl_NoPassQty = A.NoPassQty, RunCard_list.rnl_CaseID = A.CaseID, RunCard_list.rnl_Sign = A.Sign, RunCard_list.rnl_Reason = A.Reason, RunCard_list.rnl_Remark = A.Remark from OPS01 A where A.OPS_Lot_No = RunCard_list.rnl_rnc_LotNo and A.OPS_P_Sec = RunCard_list.rnl_P_Sec and A.OPS_Seq_No = RunCard_list.rnl_Seq_No and A.OPS_InputFC =:v_InputFC

narcysion你好： update RunCard_list set RunCard_list.rnl_Mac_ID = A.Mac_ID, RunCard_list.rnl_Clk_No = A.Clk_No, RunCard_list.rnl_B_Date = A.TxBDateTime, RunCard_list.rnl_E_Date = A.TxEDateTime, RunCard_list.rnl_ClassType = A.ClassType, RunCard_list.rnl_IpqcQty = A.IpqcQty, RunCard_list.rnl_PassQty = A.PassQty, RunCard_list.rnl_NoPassQty = A.NoPassQty, RunCard_list.rnl_CaseID = A.CaseID, RunCard_list.rnl_Sign = A.Sign, RunCard_list.rnl_Reason = A.Reason, RunCard_list.rnl_Remark = A.Remark from OPS01 A去除这行，试试 where A.OPS_Lot_No = RunCard_list.rnl_rnc_LotNo and A.OPS_P_Sec = RunCard_list.rnl_P_Sec and A.OPS_Seq_No = RunCard_list.rnl_Seq_No and A.OPS_InputFC =:v_InputFC 看看update帮助： UPDATE table_reference SET column_ref = update_atom [, column_ref = update_atom...] [WHERE predicates] 發表人 - deity 於 2004/04/08 17:05:36

narcysion你好： update RunCard_list set RunCard_list.rnl_Mac_ID = A.Mac_ID, RunCard_list.rnl_Clk_No = A.Clk_No, RunCard_list.rnl_B_Date = A.TxBDateTime, RunCard_list.rnl_E_Date = A.TxEDateTime, RunCard_list.rnl_ClassType = A.ClassType, RunCard_list.rnl_IpqcQty = A.IpqcQty, RunCard_list.rnl_PassQty = A.PassQty, RunCard_list.rnl_NoPassQty = A.NoPassQty, RunCard_list.rnl_CaseID = A.CaseID, RunCard_list.rnl_Sign = A.Sign, RunCard_list.rnl_Reason = A.Reason, RunCard_list.rnl_Remark = A.Remark from OPS01 A[/red]去除这行，试试 where A.OPS_Lot_No = RunCard_list.rnl_rnc_LotNo and A.OPS_P_Sec = RunCard_list.rnl_P_Sec and A.OPS_Seq_No = RunCard_list.rnl_Seq_No and A.OPS_InputFC =:v_InputFC 看看update帮助： UPDATE table_reference SET column_ref = update_atom [, column_ref = update_atom...] [WHERE predicates] sorry,点多一份,烦版主或站务人员删除[/red] ——行径窄处，留一步与人行—— 發表人 - deity 於 2004/04/08 17:23:57

Hi narcysion,    Oracle 中，似乎不接受此種寫法，你可以試試

UPDATE RUNCARD_LIST
SET RUNCARD_LIST.RNL_MAC_ID =  (SELECT  A.MAC_ID
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_CLK_NO =      (SELECT  A.CLK_NO
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_B_DATE =      (SELECT  A.TXBDATETIME
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_E_DATE =      (SELECT  A.TXEDATETIME
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_CLASSTYPE =   (SELECT  A.CLASSTYPE
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_IPQCQTY =     (SELECT  A.IPQCQTY
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_PASSQTY =     (SELECT  A.PASSQTY
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_NOPASSQTY =   (SELECT  A.NOPASSQTY
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_CASEID =      (SELECT  A.CASEID
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_SIGN =        (SELECT  A.SIGN
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_REASON =      (SELECT  A.REASON
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC),
RUNCARD_LIST.RNL_REMARK =      (SELECT  A.REMARK
                                FROM    OPS01 A
                                WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
                                AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
                                AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
                                AND     A.OPS_INPUTFC =:V_INPUTFC)    

或是用 PL/SQL

DECLARE
    OPS01_REC OPS01%ROWTYPE;   
BEGIN
    BEGIN
        SELECT  A.MAC_ID,
                A.CLK_NO,
                A.TXBDATETIME,
                A.TXEDATETIME,
                A.CLASSTYPE,
                A.CLASSTYPE,
                A.PASSQTY,
                A.NOPASSQTY,
                A.CASEID,
                A.SIGN,
                A.REASON,
                A.REMARK
        INTO    OPS01_REC.MAC_ID,
                OPS01_REC.CLK_NO,
                OPS01_REC.TXBDATETIME,
                OPS01_REC.TXEDATETIME,
                OPS01_REC.CLASSTYPE,
                OPS01_REC.CLASSTYPE,
                OPS01_REC.PASSQTY,
                OPS01_REC.NOPASSQTY,
                OPS01_REC.CASEID,
                OPS01_REC.SIGN,
                OPS01_REC.REASON,
                OPS01_REC.REMARK
        FROM    OPS01 A
        WHERE   A.OPS_LOT_NO = RUNCARD_LIST.RNL_RNC_LOTNO
        AND     A.OPS_P_SEC = RUNCARD_LIST.RNL_P_SEC
        AND     A.OPS_SEQ_NO = RUNCARD_LIST.RNL_SEQ_NO
        AND     A.OPS_INPUTFC = :V_INPUTFC;
        UPDATE  RUNCARD_LIST
        SET     RUNCARD_LIST.RNL_MAC_ID = OPS01_REC.MAC_ID,
                RUNCARD_LIST.RNL_CLK_NO = OPS01_REC.CLK_NO,
                RUNCARD_LIST.RNL_B_DATE = OPS01_REC.TXBDATETIME,
                RUNCARD_LIST.RNL_E_DATE = OPS01_REC.TXEDATETIME,
                RUNCARD_LIST.RNL_CLASSTYPE = OPS01_REC.CLASSTYPE,
                RUNCARD_LIST.RNL_IPQCQTY = OPS01_REC.CLASSTYPE,
                RUNCARD_LIST.RNL_PASSQTY = OPS01_REC.PASSQTY,
                RUNCARD_LIST.RNL_NOPASSQTY = OPS01_REC.NOPASSQTY,
                RUNCARD_LIST.RNL_CASEID = OPS01_REC.CASEID,
                RUNCARD_LIST.RNL_SIGN = OPS01_REC.SIGN,
                RUNCARD_LIST.RNL_REASON = OPS01_REC.REASON,
                RUNCARD_LIST.RNL_REMARK = OPS01_REC.REMARK;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;
END;

發表人 -

------
Fishman