Re: [SQL ] Oracle update多筆資料寫法

[fantasyj (如夢似幻)]

試試看

UPDATE TABLExxx A
   SET A.Col.A = (SELECT (CASE
                            WHEN SUBSTR(Col.A,1,1) = '1'
                              THEN 'A'
                            WHEN SUBSTR(Col.A,1,1) = '2'
                              THEN 'B'
                            WHEN SUBSTR(Col.A,1,1) = '3'
                              THEN 'C'
                            WHEN SUBSTR(Col.A,1,1) = '4'
                              THEN 'D'
                          END)
                    FROM TABLExxx B
                   WHERE B.PrimaryKey = A.PrimaryKey)
 WHERE A.PrimaryKey IN ('XXXX', 'YYYY', 'ZZZZ');

或者是寫一段PL/SQL的LOOP來做(當然效率會比較差一點)
BEGIN
  FOR A IN (SELECT (CASE
                      WHEN SUBSTR(Col.A,1,1) = '1'
                        THEN 'A'
                      WHEN SUBSTR(Col.A,1,1) = '2'
                        THEN 'B'
                      WHEN SUBSTR(Col.A,1,1) = '3'
                        THEN 'C'
                      WHEN SUBSTR(Col.A,1,1) = '4'
                        THEN 'D'
                    END) Col.A,
                   A.PrimaryKey
              FROM TABLExxx A
             WHERE A.PrimaryKey IN ('XXXX', 'YYYY', 'ZZZZ')) LOOP
    UPDATE TABLExxx B
       SET B.Col.A = A.Col.A
     WHERE B.PrimaryKey = A.PrimaryKey;
  END LOOP;
END;


--
※ 發信站: 批踢踢實業坊(ptt.cc) 
◆ From: 202.3.165.131