Why are my attempts to update this table with an IsNull failing (updating the column vals to Null)?

Question

0.00/5 (No votes)

See more:

I have this code:

SQL

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:

SQL

- 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:

SQL

OR WVItemCode=#tempCombined.MemberItemCode

...so that the final Where clause was:

SQL

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:

SQL

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:

HTML

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

Posted 15-Jan-16 8:40am

B. Clay Shannon

Updated 15-Jan-16 10:49am

v4

Add a Solution

Comments

Wendelius 15-Jan-16 14:50pm

The explanation is a bit confusing, you're not updating MemberItemCode nor PAItemCode. Instead you update WVItemCode. Or did I misunderstant the question?

B. Clay Shannon 15-Jan-16 14:53pm

Sorry, there shouldn't be a "PA" in there; I fixed it.

Wendelius 15-Jan-16 14:55pm

What about this sentence "all the MemberItemCode vals become NULL. Shouldn't they always be either their original values or 'X'?" As said, you're not updating MemberItemCode at all :confused:

B. Clay Shannon 15-Jan-16 15:06pm

Yeah, my bad again; it's the WVItemCode column that should be updated.

ZurdoDev 15-Jan-16 15:19pm

You don't show a PAITemCode column. I'm very confused.

skydger 15-Jan-16 15:35pm

Does the query:
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)
return something? Try to comment update clause and just figure out it.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-01-15T09:49:00

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

SQL

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

Ashish Raj Mishra · Answer 2 · 2016-01-15T23:11:00

Solution 2

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

Posted 15-Jan-16 23:11pm

Ashish Raj Mishra