时间戳

首页 > 解决方案 > 更新语句 SQL 不会更新每条记录

sql - 更新语句 SQL 不会更新每条记录

问题描述

问题 我已经尝试解决这个问题有一段时间了。但我没有更接近修复它。我选择了一组有资格获得续约的人。现在我想用特定的代码更新每个人，但有些记录是空白的。

我尝试过 的这些是我正在使用的查询。首先选择记录：

INSERT INTO SELECTION (CLIENDTID, CREATED_DT, FIRSTNAME, MIDDLENAME, LASTNAME, EMAIL, CONTRACTEND_DATE, PRODUCT, MOBILE,TELEPHONE, STREET, HOUSENUMBER, ADDITIVE, POSTALCODE, CITY)

SELECT CLIENDTID, GETDATE(),FIRSTNAME, MIDDLENAME, LASTNAME, EMAIL, CONTRACTEND_DATE, PRODUCT, MOBILE,TELEPHONE, STREET, HOUSENUMBER, ADDITIVE, POSTALCODE, CITY
FROM CONTRACTS C (NOLOCK)
INNER JOIN OPTINS O (NOLOCK) ON O.CLIENTID = C.CLIENTID
INNER JOIN HISTORY HIS(NOLOCK) ON HIS.CLIENTID = C.CLIENTID
WHERE 
(
    ((DATEDIFF(DD,CURRENT_TIMESTAMP, CONTRACTEND_DATE) BETWEEN 26 AND 28) 
    AND 
    (O.MAIL=1 OR O.SMS=1 OR O.DM=1 OR 0.TELEPHONE=1 AND HIS.HISTORY IS NULL))
OR
    ((DATEDIFF(DD,CURRENT_TIMESTAMP, CONTRACTEND_DATE) BETWEEN 19 AND 21) 
    AND  
    (HIS.HISTORY<10 OR HIS.HISTORY IS NULL)
    AND 
    O.SMS=1 AND C.MOBILE IS NOT NULL)
OR  
    ((DATEDIFF(DD,CURRENT_TIMESTAMP, CONTRACTEND_DATE) BETWEEN 19 AND 21) 
    AND  
    (HIS.HISTORY<100 OR HIS.HISTORY IS NULL)
    AND 
    (O.SMS=0 OR C.MOBILE IS NULL) 
    AND 
    O.CALL=1  
    AND 
    (C.MOBILE IS NOT NULL OR C.TELEPHONE IS NOT NULL))
OR
    ((DATEDIFF(DD,CURRENT_TIMESTAMP,CONTRACTEND_DATE) BETWEEN 12 AND 14) 
    AND  
    (HIS.HISTORY<100 OR HIS.HISTORY IS NULL) 
    AND 
    O.TELEPHONE=1 
    AND 
    (C.MOBILE IS NOT NULL OR C.TELEPHONE IS NOT NULL))
)

然后我使用这个查询来更新记录。

UPDATE S
SET CODE = CASE 
                WHEN ( DATEDIFF(DD, CURRENT_TIMESTAMP, C.CONTRACTEND_DATE) BETWEEN 26 AND 28)  AND HIS.HISTORY IS NULL AND O.MAIL = 1 AND C.MAIL IS NOT NULL THEN 'MAIL'
                WHEN ( DATEDIFF(DD, CURRENT_TIMESTAMP, C.CONTRACTEND_DATE) BETWEEN 26 AND 28)  AND HIS.HISTORY IS NULL AND O.DM = 1 AND (O.MAIL=0 OR C.MAIL IS NULL) THEN 'DM'
                WHEN ( DATEDIFF(DD, CURRENT_TIMESTAMP, C.CONTRACTEND_DATE) BETWEEN 26 AND 28)  AND HIS.HISTORY IS NULL AND O.DM = 0 AND (O.MAIL=0 OR C.MAIL IS NULL) AND O.SMS=1 AND C.MOBILE IS NOT NULL THEN 'SMS'    
                WHEN ( DATEDIFF(DD, CURRENT_TIMESTAMP, C.CONTRACTEND_DATE) BETWEEN 26 AND 28)   AND HIS.HISTORY IS NULL AND O.DM = 0 AND (O.MAIL=0 OR C.MAIL IS NULL) AND (O.SMS=0 OR C.MOBILE IS NULL) AND 
                              O.TELEPHONE=1 AND (C.MOBILE IS NOT NULL OR C.TELEPHONE IS NOT NULL) THEN 'EXPORT'                   
                WHEN ( DATEDIFF(DD, CURRENT_TIMESTAMP, C.CONTRACTEND_DATE) BETWEEN 19 AND 21) AND (HIS.HISTORY<10 OR HIS.HISTORY IS NULL)
                              AND O.SMS=1 AND C.MOBILE IS NOT NULL THEN 'SMS'   
                WHEN ( DATEDIFF(DD, CURRENT_TIMESTAMP, C.CONTRACTEND_DATE) BETWEEN 19 AND 21) AND (HIS.HISTORY<100 OR HIS.HISTORY IS NULL)
                              AND (O.SMS=0 OR C.MOBILE IS NULL) AND O.TELEPHONE=1 AND (C.MOBILE IS NOT NULL OR C.TELEPHONE IS NOT NULL) THEN 'EXPORT'
                              
                WHEN ( DATEDIFF(DD, CURRENT_TIMESTAMP, C.CONTRACTEND_DATE) BETWEEN 12 AND 14) AND (HIS.HISTORY<100 OR HIS.HISTORY IS NULL)
                              AND O.TELEPHONE=1 AND (C.MOBILE IS NOT NULL OR C.TELEPHONE IS NOT NULL) THEN 'EXPORT'
                ELSE NULL
            END
    FROM SELECTION S(NOLOCK)
    INNER JOIN CONTRACTS C (NOLOCK) ON C.CLIENTID = S.CLIENTID
    INNER JOIN OPTINS O (NOLOCK) ON O.CLIENTID = C.CLIENTID
    INNER JOIN HISTORY HIS(NOLOCK) ON HIS.CLIENTID = C.CLIENTID
    WHERE S.CREATED_DT>DATEADD(hh,-4,GETDATE())

所以基本上这是我用来提取记录的相同选择。但是在更新它们时，有不少是空白的。当我检查空白记录时，他们应该得到一个代码。

也许声明不是解决方法的情况，但我不知道还有什么方法可以解决这个问题。

标签： sqlsql-server

解决方案

假设使用NOLOCK不会通过允许“脏读”来引入数据异常，我看到了几种可能性，为什么不是SELECTION表中的所有数据都被更新了。

  1. 有INSERT子句S.CREATED_DT>DATEADD(hh,-4,GETDATE())。如果在INSERT4 小时之前运行，则不会更新UPDATE由此创建的行。INSERT
  2. 你UPDATE的 for EXPORT(with BETWEEN 19 AND 21days) 有一个条件O.TELEPHONE = 1while the INSERTuses O.CALL = 1。我猜后者是正确的，您需要UPDATE相应地修改代码。
  3. WHERE您的子句 (for BETWEEN 26 AND 28)的第一部分有一些与字段更新INSERT相关的奇怪逻辑。HISTORY我认为相关代码应该是我在下面给出的。操作顺序（AND优先于OR）意味着我的代码不等同于你的代码）。
  4. 记录集可能会以其他方式BETWEEN 26 AND 28引入问题，因为那里的代码根本不等效，并且似乎依赖于业务逻辑而不是逻辑等效性。

修订后26-28的代码INSERT

((DATEDIFF(DD,CURRENT_TIMESTAMP, CONTRACTEND_DATE) BETWEEN 26 AND 28) 
AND 
HIS.HISTORY IS NULL
AND 
(O.MAIL=1 OR O.SMS=1 OR O.DM=1 OR O.TELEPHONE=1)

注意：我假设这0.TELEPHONE是一个错字，应该是O.TELEPHONE.

不同的方法

如果您确信这两段代码之一是正确的，我建议您在所有相关位置使用完全相同的代码。这是如何做到这一点的简化版本：

INSERT INTO SELECTION
SELECT *
FROM SOURCETABLE t
WHERE
(
CASE
    WHEN t.A=1 THEN 'A'
    WHEN t.B=1 THEN 'B'
    ELSE NULL
END
) IS NOT NULL

UPDATE s
SET s.Target = 
    CASE
        WHEN t.A=1 THEN 'A'
        WHEN t.B=1 THEN 'B'
        ELSE NULL
    END
FROM
SELECTION s
JOIN SOURCETABLE t ON s.ID = t.ID
WHERE
    (
    CASE
        WHEN t.A=1 THEN 'A'
        WHEN t.B=1 THEN 'B'
        ELSE NULL
    END
    ) IS NOT NULL

推荐阅读