为什么 Ansi_nulls 不起作用? [英] Why Ansi_nulls does not work?

问题描述

假设我有 2 个名为 aTable1、aTable2 的表

Assume that I have 2 tables named aTable1, aTable2

aTable1 将用户 ID 设置为身份并包含以下数据:

aTable1 has userID set to identity and contains the following data:

userID  email          FirstName    LastName
1       NULL               C            CC
2       NULL               D            DD
3       [e-mail]        A            AA
4       [e-mail]        B            BB
5       [e-mail]        E            EE
6       [e-mail]        NULL         NULL
7       [e-mail]        NULL         NULL

aTable2 包含以下数据:

aTable2 contains the following data:

userID  email          FirstName    LastName    Title
3       [e-mail]      A            AA       student
4       [e-mail]      B            BB       student
5       [e-mail]      E            EE       student
NULL    NULL             C            CC       dean
NULL    NULL             D            DD       advisor
NULL    [e-mail] NULL    NULL               student2
NULL    [e-mail] NULL    NULL               student3

我想根据 aTable1 更新 aTable2.userID，但知道 2 个表中有空值，所以我这样做:

I want to update aTable2.userID based on aTable1, but knowing that 2 tables have null values in it, so i do like this:

set ANSI_NULLS off
update aTable2
set aTable2.userID = a.userID
from aTable a, aTable2 b 
where a.FirstName = b.FirstName and a.LastName = b.LastName and a.email = b.email

但是，这次更新并没有更新所有的用户ID，实际上它只更新那些电子邮件不等于空的，但我已经将ANSI_NULLS设置为关闭.我做错了什么?

However, what this update does not update all the userID, in fact, it only updates those that have email not equal to null, but I already set ANSI_NULLS to off. What did I do wrong?

推荐答案

要使更新查询起作用，您可以尝试以下操作:

To get your update query to work, you can try something like this:

UPDATE a2
SET
   userId = a.UserId
FROM
    aTable2 a2
    JOIN aTable1 a ON 
        ISNULL(a.Email,'NULL') = ISNULL(a2.Email,'NULL') AND 
        ISNULL(a.FirstName,'NULL') = ISNULL(a2.FirstName,'NULL') AND
        ISNULL(a.LastName,'NULL') = ISNULL(a2.LastName,'NULL')

当值为 NULL 时，我已将值任意设置为NULL"——使用一些不会出现在您的数据中的不同值以确保您不会收到误报.

When the values are NULL, I've arbitrarily set the value to 'NULL' -- use some distinct value that will not appear in your data to ensure you won't receive false positives.

我还看到了在 JOIN 中使用 OR 条件并检查两个值是否为 NULL 的其他解决方案:

I've also seen other solutions using OR criteria in the JOIN and checking both values for NULL:

((a.Email = a2.Email) OR (a.Email IS NULL AND a2.Email IS NULL)) ...

祝你好运.

这篇关于为什么 Ansi_nulls 不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！