作者fantasyj (如梦似幻)
看板Database
标题Re: [SQL ] Oracle update多笔资料写法
时间Thu Apr 22 15:07:21 2010
试试看
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