内部联接更新语句命令未正确结束

Question

下面的update语句是我第一次尝试在update语句上使用内部联接。我有语法错误，但我不确定在哪里，但我一直得到命令没有正确结束错误。有人能帮忙吗。

UPDATE m_rqgrechunk a
SET    a.persondept = b.persondept
FROM   m_rqgrechunk a
       INNER JOIN m_person_persondept_hist b
         ON a.person = b.person
WHERE  ( ( b.from_date IS NULL
           AND b.to_date IS NULL )
          OR ( b.from_date IS NULL
               AND a.create_date < b.to_date )
          OR ( a.create_date >= b.from_date
               AND a.create_date < b.to_date )
          OR ( a.create_date >= b.from_date
               AND a.to_date IS NULL ) )
       AND a.persondept IS NULL 

Answer 1

这就是在Oracle:中实现的方法

UPDATE m_rqgrechunk a
SET    a.persondept = 
       ( SELECT b.persondept
         FROM m_person_persondept_hist b
         WHERE a.person = b.person
           AND ( ( b.from_date IS NULL
                   AND b.to_date IS NULL )
              OR ( b.from_date IS NULL
                   AND a.create_date < b.to_date )
              OR ( a.create_date >= b.from_date
                   AND a.create_date < b.to_date )
              OR ( a.create_date >= b.from_date
                   AND a.to_date IS NULL ) )
       )
WHERE a.persondept IS NULL 
  AND EXISTS 
       ( SELECT 1  
         FROM m_person_persondept_hist b 
         WHERE a.person = b.person  
           AND ( ( b.from_date IS NULL 
                   AND b.to_date IS NULL ) 
              OR ( b.from_date IS NULL  
                   AND a.create_date < b.to_date ) 
              OR ( a.create_date >= b.from_date 
                   AND a.create_date < b.to_date )
              OR ( a.create_date >= b.from_date
                   AND a.to_date IS NULL ) )
       ) ;

在这种情况下，NOT EXISTS部分并不是真正需要的，因为要更新的值是NULL，所以用NULL无效地更新它们不会有多大伤害，除了日志空间会无故膨胀。

---

另一种方法是使用派生表：

UPDATE 
( SELECT a.persondept, b.persondept AS persondept_new
  FROM m_rqgrechunk a
    JOIN m_person_persondept_hist b
      ON a.person = b.person
  WHERE ( ( b.from_date IS NULL
            AND b.to_date IS NULL )
       OR ( b.from_date IS NULL
            AND a.create_date < b.to_date )
       OR ( a.create_date >= b.from_date
            AND a.create_date < b.to_date )
       OR ( a.create_date >= b.from_date
            AND a.to_date IS NULL ) )
    AND a.persondept IS NULL
) 
SET persondept = persondept_new ;

Answer 2

加入的更新是IMHO像这样完成的

UPDATE m_rqgrechunk a
   INNER JOIN m_person_persondept_hist b
     ON a.person = b.person
SET    a.persondept = b.persondept
WHERE  ( ( b.from_date IS NULL
       AND b.to_date IS NULL )
      OR ( b.from_date IS NULL
           AND a.create_date < b.to_date )
      OR ( a.create_date >= b.from_date
           AND a.create_date < b.to_date )
      OR ( a.create_date >= b.from_date
           AND a.to_date IS NULL ) )
   AND a.persondept IS NULL