原sql如下,是一个procedure
BEGIN
UPDATE A
SET A.NCHAR1 =
(SELECT DISTINCT B.NUM1
FROM B
WHERE B.COL6 = A.COL2
AND A.NCHAR2 = B.COL7)
WHERE TRIM(A.NCHAR2) = 'SF10'
AND A.COL3 = 'WX'
AND A.COL4 = 'SF1999'
AND A.NCHAR1 <>
'(SELECT DISTINCT B.NUM1 FROM B WHERE B.COL6=A.COL2 AND A.NCHAR2=B.COL7)'
AND A.COL5 = 0;
UPDATE A
SET NCHAR1 =
(SELECT DISTINCT NUM1
FROM B
WHERE COL6 = COL2
AND NCHAR2 = COL7)
WHERE TRIM(NCHAR2) = 'WJ10'
AND COL3 = 'WX'
AND COL4 = 'WJ1999'
AND NCHAR1 <>
'(SELECT DISTINCT NUM1 FROM B WHERE COL6=COL2 AND NCHAR2=COL7)'
AND COL5 = 0;
UPDATE A
SET NCHAR1 =
(SELECT DISTINCT NUM1m
FROM B
WHERE COL6 = COL2
AND NCHAR2 = COL7)
WHERE TRIM(NCHAR2) = 'BS10'
AND COL3 = 'WX'
AND COL4 = 'BS1999'
AND NCHAR1 <>
'(SELECT DISTINCT NUM1 FROM B WHERE COL6=COL2 AND NCHAR2=COL7)'
AND COL5 = 0;
UPDATE A
SET NCHAR1 =
(SELECT DISTINCT NUM1
FROM B
WHERE COL6 = COL2
AND NCHAR2 = COL7)
WHERE TRIM(NCHAR2) = 'CD10'
AND COL3 = 'WX'
AND COL4 = 'CD1999'
AND NCHAR1 <>
'(SELECT DISTINCT NUM1 FROM B WHERE COL6=COL2 AND NCHAR2=COL7)'
AND COL5 = 0;
COMMIT;
END;
首先,这个sql都写错了
见这儿
AND NCHAR1 <>
'(SELECT DISTINCT NUM1 FROM B WHERE COL6=COL2 AND NCHAR2=COL7)'
其次,我远程连网友用desc电脑看了下,密密麻麻的 nchar ,几乎当时就晕了。
我可以不告诉你,这是一个实施顾问写的sql么。
以上语句可以用MERGE改写如下
MERGE INTO (SELECT A.NCHAR1, A.COL2, A.NCHAR2
FROM A
WHERE A.NCHAR2 IN (' SF10')
AND A.COL3 = 'WX'
AND A.COL4 ='CD1999'
AND A.COL5 = 0) A
USING (SELECT B.NUM1, B.COL6, B.COL7
FROM B
GROUP BY B.NUM1, B.COL6, B.COL7) B ON (B.COL6 = A.COL2 AND A.NCHAR2 = B.COL7)
WHEN MATCHED THEN
UPDATE
SET A.NCHAR1 = TO_CHAR(B.NUM1)
WHERE NVL(TRIM(A.NCHAR1),' ') <> TO_CHAR(B.NUM1)