坑人的sql(MERGE改写UPDATE)

最新推荐文章于 2024-05-14 17:45:51 发布

原创最新推荐文章于 2024-05-14 17:45:51 发布 · 1.5k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

改写优化专栏收录该内容

28 篇文章

订阅专栏

本文探讨了一段复杂的SQL更新语句，并展示了如何将其优化为更简洁易读的MERGE语句。通过对原始过程化SQL的分析，指出了其中存在的错误并提出了改进方案。

原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)