Set the value of a column in one table based on the sum of column values in another table 1

Hello

I would like to update the column in one table based on the results of a query from another table.

I have two tables IM_ITEM and IM_INV tables. In the IM_INV table I would like to sum up the location quantities for each item. If the quantity is zero, I would like to set the DISC_COD to null for that item.


So for example, the tables contents are as follows:

IM_ITEM Table

ITEM_NO DISC_COD
------- -------
1001 D10
1002 D25
1003 <null>
1004 D10



IM_INV Table

ITEM_NO LOC QTY
------- ------- ------
1001 01 5
1001 02 4
1002 01 0
1002 02 0
1003 01 5
1003 02 0
1004 01 0
1004 02 0



After, I run the query, we would like the values in the IM_ITEM table to look as follows:


IM_ITEM Table

ITEM_NO DISC_COD
------- -------
1001 D10
1002 <null>
1003 <null>
1004 <null>


Can someone help me with the SQL command to accomplish this?

Regards,

 

[SkipVought]

CarrahaG,

Over the past 5 years you have posted 37 questions and received many helpful replies from Tek-Tip members. Yet, you have posted only ONE customary "THANK YOU" for these valuable posts. In fact there are some threads that you have left hanging with NO RESPONSE to other Tek-Tip responders.

At Tek-Tips it is most fitting when it is appropriate to...

[blue]
Thank Member
for this valuable post![/blue]

These [purple]Little Purple Stars[/purple] identify threads for Tek-Tips browsers, as ones that have been helpful. They also act as a token "Thank You" to the one or ones who posted valuable information. And they identify the recipient as a grateful member.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gmmastros]

@CarrahaG,

In your sample data, you have item number 1003.

[tt]1003 01 5
1003 02 0[/tt]

The sum of the QTY is 5 (not 0), but your expected output is NULL. Is this a mistake with your sample data, or is it a mistake with my understanding of your question?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CarrahaG]

Hi Skip

I have always thanked members with a formal reply who have responded to my questions in which they have been helpful. It is many more times than once.

In several cases, I did not get a helpful response in time and I had to find an alternative solution. In those cases where the alternative solution worked, I did not look back at the continued responses to my original request. I do agree this is wrong of me and I will definitely try to to correct this from my end.

I will also use the purple stars along with my thank you responses in the future.

Regards,

 

[CarrahaG]

Hi George

The resulting table is correct.

The reason the value for DISC_COD is null for ITEM_NO 1003 in the resulting table is because it was null in the first place.

Regards,
Georges

 

[gmmastros]

Georges,

There's likely to be several different ways you could run the query. This is just one of them....

First, I would get a list if item numbers where the sum of the quantity = 0, like this.

Select ITEM_NO
From   IM_INV
GROUP BY ITEM_NO
Having Sum(QTY) = 0

When you run the query above, you should see a list of item numbers where the SUM of Quantity is 0. We can then use the list of item numbers to join back to the other table to do the update, like this..

Update	IM_ITEM
Set     IM_ITEM.DISC_COD = NULL
From    IM_ITEM
        Inner Join (
          Select ITEM_NO
          From   IM_INV
          GROUP BY ITEM_NO
          Having Sum(QTY) = 0
          ) As A
          ON IM_ITEM.ITEM_NO = A.ITEM_NO
Where   IM_ITEM.DISC_COD Is Not NULL

Notice the final where clause. If the value is already null, we don't change it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CarrahaG]

Hello George

Your script worked perfectly. Thank you.

Regards,
Georges

 

[SkipVought]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]