如何使用结果集更新其他的表？谢谢了！

Question

具体实现的功能就是用  表1  关联  表2后的结果集中的某个字段，去批量更新 表3 中间的某个字段。
表1 和 表2 关联的结果集中 包含所有表三的主键
使用一句SQL完成，不使用游标。
谢谢了

Answer 1

FYI:--TABLE: A (ID,NAME,CID);
--TABLE: B (ID,NAME);
--TABLE: C (ID,NAME);-- 1ST WAY:                  
UPDATE (SELECT B.NAME BNAME,
               C.NAME CNAME
          FROM A,
               B,
               C
         WHERE A.ID = B.ID
           AND A.CID = C.ID)
   SET CNAME = BNAME;
   
-- 2ND WAY:
UPDATE C
   SET C.NAME = (
                 SELECT B.NAME
                   FROM A,
                        B
                  WHERE A.ID = B.ID
                    AND A.CID = C.ID
                )
 WHERE EXISTS (
               SELECT 1
                 FROM A,
                      B
                WHERE A.ID = B.ID
                  AND A.CID = C.ID 
              );

Answer 2

首先感谢您的及时回答具体情况如下
--TABLE: A (PK0,PK1,PK3,PK1_B，ITEM_A_01,ITEM_A_02,ITEM_A_03);   PK(PK0,PK1,PK3)实际情况是PK1 = PK1_B的
--TABLE: B (PK0,PK1,ITEM_B_01);   PK(PK0,PK1)
--TABLE: C (PK3,PK4,ITEM_C_01,ITEM_C_02,ITEM_C_03,ITEM_C_04);   PK(PK3,PK4)情况是这样   A与B  相连(A.PK0 = B.PK0  AND A.PK1_B= B.PK1  A) 
取得  ITEM_A_01,ITEM_A_02,ITEM_A_03(取得的可能是一条数据也可能是一批数据)
        
SET   
ITEM_C_01 = ITEM_A_01
ITEM_C_02 = ITEM_A_02
ITEM_C_03 = ITEM_A_03
在此麻烦您了

Answer 3

能不能用LEFT JOIN  进行表链，不用WHERE

Answer 4

    UPDATE (SELECT
                     T501_AEON_ORDER.T501_WAREH_CD AS T501_WAREH_CD,
                     T031_SHIPMENT_REQ_IMP_HIST.T031_WAREH_CD AS T031_WAREH_CD
                     FROM T501_AEON_ORDER
                     LEFT JOIN M029_DESTINATION ON T501_AEON_ORDER.T501_WAREH_CD =  M029_DESTINATION.M029_WAREH_CD
                                                AND T501_AEON_ORDER.T501_OWNER_CD = M029_DESTINATION.M029_OWNER_CD
                                                AND RTRIM(T501_AEON_ORDER.T501_CUSTOM_CD2) =
                                                    RTRIM(M029_DESTINATION.M029_CUSTOM_CD)
                                                AND RTRIM(T501_AEON_ORDER.T501_SHOP_CD) =
                                                    RTRIM(M029_DESTINATION.M029_SHIP_TO_CD)
                                                AND M029_DESTINATION.M029_TRAN_KBN < '3'
                     LEFT JOIN T031_SHIPMENT_REQ_IMP_HIST
                     ON T501_AEON_ORDER.T501_SLIP_NO = T031_SHIPMENT_REQ_IMP_HIST.T031_SEND_NO)
SET T031_WAREH_CD = T501_WAREH_CD这样会出现 01779的错误

Answer 5

-- A.PK3 = C.PK3 ?
UPDATE (SELECT A.ITEM_A_01 "ITEM_A_01",
               A.ITEM_A_02 "ITEM_A_02",
               A.ITEM_A_03 "ITEM_A_03",
               C.ITEM_C_01 "ITEM_C_01",
               C.ITEM_C_02 "ITEM_C_02",
               C.ITEM_C_03 "ITEM_C_03"
          FROM A, B, C
         WHERE A.PK0 = B.PK0
           AND A.PK1_B = B.PK1
           AND A.PK3 = C.PK3)
   SET (ITEM_C_01, ITEM_C_02, ITEM_C_03) = (ITEM_A_01, ITEM_A_02, ITEM_A_03);
UPDATE C
   SET (ITEM_C_01, ITEM_C_02, ITEM_C_03) = (SELECT ITEM_A_01,
                                                   ITEM_A_02,
                                                   ITEM_A_03
                                              FROM A, B
                                             WHERE A.PK0 = B.PK0
                                               AND A.PK1_B = B.PK1
                                               AND A.PK3 = C.PK3)
 WHERE EXISTS (SELECT 1
                 FROM A, B
                WHERE A.PK0 = B.PK0
                  AND A.PK1_B = B.PK1
                  AND A.PK3 = C.PK3);如果存在多个值去更新一个字段的VALUE,这样会报错的。具体看你的需求了. GOOD LUCK!

Answer 6

T031_SHIPMENT_REQ_IMP_HIST 
主键  
倉庫コード T031_WAREH_CD
寄託者コード T031_OWNER_CD
取込ID         T031_GET_ID
送信管理NO T031_SEND_NOM029_DESTINATION
主键
倉庫コード M029_WAREH_CD
寄託者コード M029_OWNER_CD
取引先コード M029_CUSTOM_CD
納入者コード M029_SHIP_TO_CDT501_AEON_ORDER
主键
倉庫コード T501_WAREH_CD
寄託者コード T501_OWNER_CD
取引先コード１ T501_CUSTOM_CD1
伝票番号          T501_SLIP_NO
行NO         T501_LINE_NO