为什么我尝试使用IsNull更新此表失败（将列val更新为Null）？ [英] Why are my attempts to update this table with an IsNull failing (updating the column vals to Null)?
问题描述
我有这段代码：
创建 表＃temp1
（
MemberNo varchar （ 25 ），
MemberItemCode varchar （ 25 ），
ShortName varchar （ 25 ），
ItemCode varchar （ 50 ），
WVItem varchar （ 25 ），
WVItemCode varchar （ 25 ），
WVDescription varchar （ 250 ），
Week1Usage varchar （ 25 ），
Week1Price varchar （ 25 ）
）
- 填充week1 vals
INSERT INTO ＃temp1（MemberNo，MemberItemCode，WVItemCode，WVDescription，Week1Usage，Week1Price）
选择 MemberNo，ItemCode，WVItemCode，Description，QtyShipped，Price
FROM InvoiceDetail
WHERE 单位= @单位
AND InvoiceDate BETWEEN @ BegDate AND @ Week1End ;
...成功填充了MemberItemCode列。
但是，如果我稍后尝试更新WVItemCode列，如下所示：
- 这应该是 update WVItemCode 或 set it to ' X'
更新＃temp1 设置
WVItemCode = isnull（（选择 top 1 ItemCode From MasterMemberUnitMapping where Unit = @ Unit 和
MemberNo =＃temp1.MemberNo 和 MemberItemCode =＃temp1.MemberItemCode （选择 ItemCode 和 ItemCode code-keyword>来自 UnitProducts 其中
Unit = @ Unit）），' X'）
- 这应该是 update WVItemCode 或 设置返回 ＃temp1.MemberItemCode
更新＃temp1 set
WVItemCode = isnull（（选择 top 1 ItemCode 从 MasterMemberMapping 其中 MemberNo =＃temp1.MemberNo 和 MemberItemCode +
PackType = ＃temp1.Member ItemCode），＃temp1.MemberItemCode）
其中 WVItemCode = ' X'
...所有的wvItemCode值都是NULL。它们不应该总是原始值或X吗？
我理解更新代码的方式，第一次更新应该更新WVItemCode或将其设置为'X'然后第二次更新应该更新它，如果它是'X'或者将其设置回原来的＃temp1.MemberItemCode值。
我也尝试过附加以下内容：
或 WVItemCode =＃tempCombined.MemberItemCode
......所以最终的Where子句是：
< pre lang =SQL> 其中 WVItemCode = ' X ' 或 WVItemCode =＃temp1.MemberItemCode
。 ..但是，除了Null MemberItemCode val之外，它仍然只留给我。这些更新如何使该列无效？当我省略它们时，我得到了MemberItemCode值。
更新
我找不到它现在，但有人推荐了UNION，我用它。但是：
我希望UNION可以工作：
DECLARE @ Week1End datetime = DATEADD（Day ， 6 ， @ BegDate ）;
DECLARE @ Week2Begin datetime = DATEADD（日， 7 ， @ BegDate ）;
创建 表＃temp1
（
MemberNo varchar （ 25 ），
MemberItemCode varchar （ 25 ），
ShortName varchar （ 25 ），
ItemCode varchar （ 50 ），
PAItem varchar （ 25 ），
PAItemCode varchar （ 25 ），
PADescription varchar （ 250 ），
Week1Usage varchar （ 25 ），
Week1Price varchar （ 25 ），
Week2Usage varchar （ 25 ），
Week2Price varchar （ 25 ）
）
INSERT INTO ＃temp1（MemberNo，MemberItemCode，PADescription，Week1Usage，Week1Price，Week2Usage，Week2Price）
SELECT MEMBERNO，ITEMCODE， DESCRIPTION，QTYSHIPPED，PRICE， NULL ， NULL
FROM INVOICEDETAIL
WHERE UNIT = @ Unit
AND INVOICEDATE BETWEEN @ BegDate AND @ Week1End
UNION ALL
SELEC T MEMBERNO，ITEMCODE，DESCRIPTION， NULL ， NULL ，QTYSHIPPED，PRICE
FROM INVOICEDETAIL
WHERE UNIT = @ Unit
AND INVOICEDATE BETWEEN @ Week2Begin AND @ EndDate ;
...和它首先看起来它确实如此，但后来我注意到结果集中的每一行总是有两个NULL值 - 首先是两个Week2值，然后是两个Week1值，如下所示：
第1周使用周1价格周2使用周2价格
------------ --------- --- ------------ ------------
1.000 163.530 NULL NULL
NULL NULL 1.000 163.530
我需要的是：
第1周使用周1价格周2使用周2价格
------------ ------------ -------- ---- ------------
1.000 163.530 1.000 163.530
有没有用UNION做这个的方法，还是我需要回到我的3个表的想法（第1周的临时表，第2周的另一个，第三个结合它们）？
可能有几个原因，例如表中＃temp1.MemberItemCode中是否包含NULL值。在这种情况下，第二个查询可以将所有X更新为null。根据第一次更新的结果，这可能会影响所有行等。
由于我们没有您的实际数据，因此无法测试更新，但是您可以做的是部分运行它们。运行第一个查询后，执行选择以查看数据。如果一切正常，请运行第一次更新并再次检查数据，依此类推。
如果这不能解释行为，则下一步可能是修改更新选择语句以查看每行的内容。例如，第一次更新可以转换为类似
更新 a。*，
isnull（（选择 top 1 ItemCode
从 MasterMemberUnitMapping
其中单位= @单位
和 MemberNo =＃temp1.MemberNo
和 MemberItemCode =＃temp1.MemberItemCode
> 中的（选择 ItemCode
来自 UnitProducts
其中单位= @单位
）
），' X'） AS New_WVItemCode
FROM ＃temp1 a
这应该有助于查看相关性是否正常工作以及是否ISNULL给出了预期的结果
我无法理解你从哪里获得包装类型
MemberItemCode +
PackType =＃temp1.MemberItemCode
如果你喜欢这个
MemberItemCode =＃temp1 .MemberItemCode
i with dummy及其工作完美
I have this code:
create table #temp1
(
MemberNo varchar(25),
MemberItemCode varchar(25),
ShortName varchar(25),
ItemCode varchar(50),
WVItem varchar(25),
WVItemCode varchar(25),
WVDescription varchar(250),
Week1Usage varchar(25),
Week1Price varchar(25)
)
-- populate week1 vals
INSERT INTO #temp1 (MemberNo, MemberItemCode, WVItemCode, WVDescription, Week1Usage, Week1Price)
select MemberNo, ItemCode, WVItemCode, Description, QtyShipped, Price
FROM InvoiceDetail
WHERE Unit=@Unit
AND InvoiceDate BETWEEN @BegDate AND @Week1End;
...which successfully populates the MemberItemCode column.
However, if I later attempt to update the WVItemCode column like so:
- this should either update WVItemCode or set it to 'X'
Update #temp1 set
WVItemCode = isnull((Select top 1 ItemCode From MasterMemberUnitMapping where Unit=@Unit and
MemberNo=#temp1.MemberNo and MemberItemCode= #temp1.MemberItemCode and ItemCode in (Select ItemCode from UnitProducts where
Unit=@Unit)), 'X')
- this should either update WVItemCode or set it back to #temp1.MemberItemCode
Update #temp1 set
WVItemCode = isnull((Select top 1 ItemCode From MasterMemberMapping where MemberNo=#temp1.MemberNo and MemberItemCode +
PackType = #temp1.MemberItemCode), #temp1.MemberItemCode)
Where WVItemCode='X'
...all the wvItemCode vals are NULL. Shouldn't they always be either their original values or 'X'?
The way I understand the Update code, the first update should either update WVItemCode or set it to 'X' and then the second update should update it if it is 'X' or else set it back to the original #temp1.MemberItemCode value.
I also tried appending the following:
OR WVItemCode=#tempCombined.MemberItemCode
...so that the final Where clause was:
Where WVItemCode='X' OR WVItemCode=#temp1.MemberItemCode
...but that still leaves me with nothing but Null MemberItemCode vals. How are these updates nullifying that column? When I omit them, I get the MemberItemCode vals.
UPDATE
I can't find it now, but somebody recommended a UNION, and I used it. However:
I was hoping the UNION would work:
DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate);
DECLARE @Week2Begin datetime = DATEADD(Day, 7, @BegDate);
create table #temp1
(
MemberNo varchar(25),
MemberItemCode varchar(25),
ShortName varchar(25),
ItemCode varchar(50),
PAItem varchar(25),
PAItemCode varchar(25),
PADescription varchar(250),
Week1Usage varchar(25),
Week1Price varchar(25),
Week2Usage varchar(25),
Week2Price varchar(25)
)
INSERT INTO #temp1 (MemberNo, MemberItemCode, PADescription, Week1Usage, Week1Price, Week2Usage, Week2Price)
SELECT MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE, NULL, NULL
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @BegDate AND @Week1End
UNION ALL
SELECT MEMBERNO, ITEMCODE, DESCRIPTION, NULL, NULL, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate;
...and it first it seemed like it did, but then I noticed that each row in the result set always has two NULL values - first the two Week2 values, then the two Week1 Values, like this:
Week 1 Usage Week 1 Price Week 2 Usage Week 2 Price
------------ ------------ ------------ ------------
1.000 163.530 NULL NULL
NULL NULL 1.000 163.530
What I need is:
Week 1 Usage Week 1 Price Week 2 Usage Week 2 Price
------------ ------------ ------------ ------------
1.000 163.530 1.000 163.530
Is there a way to do that with UNION, or do I need to go back to my 3-table idea (a temp table for week1, another for week2, and a third to combine them)?
There can be several reasons, for example does the table contain NULL values in #temp1.MemberItemCode. In such case the second query could update all X's to null. Depending on the results of the first update this could affect all rows etc.
Since we don't have your actual data it's impossible to test the updates, but what you could do is to run them in parts. After running the first query, do a select to see the data. If everything is correct run the first update and check the data again and so on.
If this doesn't explain the behaviour the next step could be to modify the updates to select statements in order to see what's going on for each row. For example the first update could be converted to something like
Update a.*, isnull((Select top 1 ItemCode From MasterMemberUnitMapping where Unit=@Unit and MemberNo=#temp1.MemberNo and MemberItemCode= #temp1.MemberItemCode and ItemCode in (Select ItemCode from UnitProducts where Unit=@Unit ) ), 'X') AS New_WVItemCode FROM #temp1 a
This should help to see if the correlation works correctly and if the ISNULL is giving expected results
i cannot able to understand from where you get pack type
MemberItemCode +
PackType = #temp1.MemberItemCode
if you just like this
MemberItemCode = #temp1.MemberItemCode
i check with dummy and its work perfectly
这篇关于为什么我尝试使用IsNull更新此表失败（将列val更新为Null）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
