Re: [SQL ] Oracle update多筆資料寫法
試試看
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
討論串 (同標題文章)