Update but only If stock qutaity = 0 1

[Cpreston]

Hi

we have a table that holds Product Information. Frome time to time we need to update these tables ,manually through our business system. Tis takes a lot of time to do.

I created a query that updates the fields but I need it to be a little more intelligent (fairly a novice in writing code)

Our ProductID will have ten rows, as we have 10 sites if I do a select *

The fields I want to update work ok like I have it however if the 2 fields called Stocklevelmin and Stocklelvemax have anything other than 0 in them then I do not want any of the 10 rows to update. below is my update statement as I have it, but I do not want it to run if any of the rows have any other figure in stocklevelmin and stocklevelmax than 0. I only want it to update all 10 rows it the figure is 0.

UPDATE ProductStockOption
SET Stocked='0', StandardPurchase='0',stockclass ='O', HowSourced = '0'
where ProductID = 34797;

Could someone advise how the code should be to achieve this please. I have looked ah If Else but cannot work out the syntax.

Thanks

 

[djj55]

UPDATE ProductStockOption
SET Stocked='0', StandardPurchase='0',stockclass ='O', HowSourced = '0'
where ProductID = 34797
AND stocklevelmin = 0
AND stocklevelmax = 0;

If I read you correctly

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Sample data and expected results would be very helpful.

-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

 

[Olaf Doschke]

but I do not want it (the update) to run if any of the rows have any other figure in stocklevelmin and stocklevelmax than 0

What you want is a condition on all site rows, that's not possible in a normal single table query, as a where clause always only is about the current record. There are several options, eg subselects in the where clause or self joins.

WHERE NOT EXISTS would be a solution here: [tt]WHERE NOT EXISTS SELECT 1 FROM ProductStockOption WHERE ProductID = 34797 AND (stocklevelmin <> 0 OR stocklevelmax <> 0)[/tt]

Bye, Olsf.

 

[Cpreston]

Hi

Slight issues which I did not think about the field stocklevelmin amd stocklevelmax some have Null values in

so the table could look like this

ProductID StocklevelMin Stocklevelmax
6559[pre][/pre]1[pre][/pre] 1
6559 [pre][/pre]NULL [pre][/pre]NULL

So for the query (I have not included the other field names as they are not part of the where issue)

UPDATE ProductStockOption
SET Stocked='0', StandardPurchase='0',stockclass ='O', HowSourced = '0'
where ProductID = 6559
AND stocklevelmin = 0
AND stocklevelmax = 0

If does not update any rows because they are NULL. How do I change this script to look at NULL instead or together with 0

I have tried isnull (please remember I am not very up on programming)

Thanks

 

[djj55]

Use

AND (stocklevelmin = 0  OR stocklevelmin IS NULL) 
 AND (stocklevelmax = 0 OR stocklevelmax IS NULL)

The parenthesis are important.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Cpreston]

Hi

Ok works ok. However my query currently is like this

UPDATE ProductStockOption
SET Stocked='0', StandardPurchase='0',stockclass ='O', HowSourced = '0'
where ProductID IN (14159,5518)
AND (stocklevelmin = 0 or stocklevelmin IS NULL)
AND (stocklevelmax = 0 or stocklevelmax IS null)

However, if I have 10 rows of data, and 9 are do not have stocklevelmin and max in but one does, it is updating the 9 rows that meet the criteria and does not touch the 10th one with figures in min and max other than 0. What I am trying to achieve is it does not update the other 9 rows if there is one row with min and max figures in. So if any of the min and max <> 0 then do not update any rows at all . is tis possible?

Thanks

 

[djj55]

If you mean if any row contains a value then NO data is updated using an EXISTS can help

UPDATE ProductStockOption
 SET Stocked='0', StandardPurchase='0',stockclass ='O', HowSourced = '0'
 where ProductID IN (14159,5518)
   AND NOT EXISTS (SELECT * FROM ProductStockOption WHERE 1 = 1
 AND (stocklevelmin = 0 or stocklevelmin IS NULL) 
 AND (stocklevelmax = 0 or stocklevelmax IS null))

I may have this wrong though

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Cpreston]

Hi

Thanks djj55 that works perfect. Could you explain what the Where 1 =1 does, understand the rest.

many thanks for the brilliant help

 

[Cpreston]

Sorry I jumped the gun before I tested correctly.

Codes 5263 and 50163 have no quantity's in min or max fields so can be updated
codes 5296 and 6810 have quantity's in min and max field so should not be updated.
The query currently does not allow any to be updated, but 5263 and 50163 should be as they have null or 0 values. Sorry

UPDATE ProductStockOption
SET Stocked='0', StandardPurchase='0',stockclass ='O', HowSourced = '0'
where ProductID IN (5263, 5016, 5296, 6810)
AND NOT EXISTS (SELECT * FROM ProductStockOption WHERE 1 = 1
AND (stocklevelmin = 0 or stocklevelmin IS NULL)
AND (stocklevelmax = 0 or stocklevelmax IS null))

 

[djj55]

I thought there might be a problem.

Notice that any criteria (WHERE clause) just needs to evaluate to true. 1 = 1 is always true, so you can use it if you want to comment out the AND...


I believe the criteria needs placed it two places, but I am not sure. (One inside the exists and one on the query.)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Sample data and expected results would be very helpful.

-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

 

[Olaf Doschke]

What you want to check is NOT EXISTS ... <>0. All records should have 0 or NULL, right? Then what should NOT exist is records with other data.

Bye, Olaf.

 

[Cpreston]

Hi

Sorry if I am not explaining myself very well. I have attached a word document which hopefully explains things better.
I could have up to 50 codes at a time to update or may be more.

Thanks

 

[Olaf Doschke]

Thank you for that verbose documentation of the problem.

It confirms what I say and that I understood ou, you jsut don't understand the solution.

If you check - as you do - [tt]NOT EXISTS (SELECT * FROM ProductStockOption WHERE (stocklevelmin = 0 or stocklevelmin IS NULL) AND (stocklevelmax = 0 or stocklevelmax IS null))[/tt]
That is false for the first example, but also false for the second example on page 1, because in both groups a record with values 0 or NULL exists. The first group differs from the second with the exception against the rule.

The idea of the EXISTS is to find an exception of the rule, that is enough to not do the update, so you have to check the opposite of what you want and negate that result.

In natural language you get a double negation this way:
1. WHERE ALL records are 0 or NULL in the two fields - this is what you say
2. WHERE NO record is <>0 and NOT NULL in the two fields - this is an equivalent term

These two terms are equivalent, the first one sounds more natural then the double negation, but the double negation is easier to verify, since you only need to come up with one example of an unwanted record. You check, if only one record fulfills the unwanted condition of having values <>0 and NOT NULL, that is sufficent to not want to update this group of recors. This is what NOT EXISTS is capable to test.

So term 2 has to be translated to WHERE NOT EXIST SELECT 1 FROM table WHERE (stocklevelmin NOT NULL and stocklevelmin<>0) OR (stocklevelmax NOT NULL and stocklevelmax<>0)

Bye, Olaf.

 

[gmmastros]

Here's how I would approach this....

First, I want to get the ProductId's that need updating. To do this, I would get the min of StockLevelMin, max of stockLevelMin , min of StockLevelMax and Max of StockLevelMax.

Like this:

Select	ProductId, 
		Coalesce(Min(StockLevelMin), 0) As MinOfMin, 
		Coalesce(Max(StockLevelMin), 0) As MaxOfMin,
		Coalesce(Min(StockLevelMax), 0) As MinOfMax,
		Coalesce(Max(StockLevelMax), 0) As MaxOfMax
From	ProductStockOption
Group By ProductId

When you run the query above, you'll notice that there is only 1 row per product id. If all of the last four columns (MinOfMin, MaxOfMin, MinOfMax and MaxOfMax) are all zero, then this you want to update all the rows that match this product id. (right?).

We can easily filter this situation, like this.

Select	ProductId, 
        Coalesce(Min(StockLevelMin), 0) As MinOfMin, 
        Coalesce(Max(StockLevelMin), 0) As MaxOfMin,
        Coalesce(Min(StockLevelMax), 0) As MinOfMax,
        Coalesce(Max(StockLevelMax), 0) As MaxOfMax
From	ProductStockOption
Group By ProductId
Having	Coalesce(Min(StockLevelMin), 0) = 0
        And Coalesce(Max(StockLevelMin), 0) = 0
        And Coalesce(Min(StockLevelMax), 0) = 0
        And Coalesce(Max(StockLevelMax), 0) = 0

When you run this query, you should see a list of product id's where all rows for stock level min and stock level max are either null or zero. Once you have this list, it's super easy to use it to filter your final update query. Like this.

UPDATE ProductStockOption
SET    Stocked='0', 
       StandardPurchase='0',
	   stockclass ='O', 
	   HowSourced = '0'
From   ProductStockOption
       Inner Join (
         Select	ProductId
         From	ProductStockOption
         Group By ProductId
         Having	Coalesce(Min(StockLevelMin), 0) = 0
                And Coalesce(Max(StockLevelMin), 0) = 0
                And Coalesce(Min(StockLevelMax), 0) = 0
                And Coalesce(Max(StockLevelMax), 0) = 0
        ) As ProductsToUpdate
        On ProductStockOption.ProductId = ProductsToUpdate.ProductId

The update code above will look at all the rows in the table, and update the rows that should be updated.

If you want to limit this to just a small handful of ProductId's, you can add a where clause, like this.

UPDATE ProductStockOption
SET    Stocked='0', 
       StandardPurchase='0',
	   stockclass ='O', 
	   HowSourced = '0'
From   ProductStockOption
       Inner Join (
         Select	ProductId
         From	ProductStockOption
         [!]Where ProductId In (5296, 5016)[/!]
         Group By ProductId
         Having	Coalesce(Min(StockLevelMin), 0) = 0
                And Coalesce(Max(StockLevelMin), 0) = 0
                And Coalesce(Min(StockLevelMax), 0) = 0
                And Coalesce(Max(StockLevelMax), 0) = 0
        ) As ProductsToUpdate
        On ProductStockOption.ProductId = ProductsToUpdate.ProductId

-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