Update Set Where - multiple rows of validation!

[Jeniferous]

I want to set a status field to P where a condition is met in an alternative table. The alternative table contains order lines but there could be more than one order line per order number but the table i'm updating has a single record per order. I've got the below code....

UPDATE OrderHeader
SET orderheader.[status]='P'
from orderheader inner join orderlines on orderheader.ordernumber= orderlines .ordernumber
WHERE orderlines.order_qty = orderlines.despatched_qty

If i run it then I don't get errors but it changes the status to P if the first instance of the order in the lines table meets the criteria. I want it to set it to P if ALL the records for that order within the lines table meet the criteria and set it to E if any don't!

Can someone help me! Hope that makes sense!

P.s. i'm a newbie to sql so keep it basic please!

 

[simian336]

Could you provide a data sample and the expected result.

Thanks

Simi

 

[Jeniferous]

Order Header:
080321
080322
080323


Order Lines:
Order_no Line_Number Product Order_qty Despatched_Qty
080321 0001 HANDLE 20 20
080321 0002 WHEELS 10 10
080321 0003 TYRES 40 0
080322 0001 WHEELS 2 2
080322 0002 WHEELS 10 0

So in my code it would send me back order 080321 as a P because the first row order qty = despatched qty but I don't want it to becaseu not all the three items 080321 have matching qty's. I want to set the status to P where all the lines on an order have order qty = despatched qty?

 

[simian336]

You did not provide a true case so I added some records.

But you need to get a count of the all records for each ordernumber as well as a count for the matching. Then update them where total = matching.

Simi


insert into OrderLines values ('080324', '0001', 'HANDLE', 20, 20)
insert into OrderLines values ('080324', '0002', 'WHEELS', 10, 10)
insert into OrderLines values ('080324', '0003', 'TYRES', 40, 40)


with tn as
(
select ordernumber, COUNT(ordernumber) as totalnumber
from orderlines
group by ordernumber
),
mat as
(
select ordernumber, COUNT(ordernumber) as matching
from orderlines
where order_qty=Despatched_Qty
group by ordernumber
)
UPDATE OrderHeader
SET orderheader.[status]='P'
from orderheader
join tn
on tn.ordernumber = OrderHeader.ordernumber
join mat
on mat.ordernumber=OrderHeader.ordernumber
where tn.totalnumber=mat.matching

 

[PWise]

how about

UPDATE OrderHeader
SET orderheader.[status]='P'
from orderheader 
Left join (
          Select Distinct ordernumber
          From  orderlines 
          WHERE orderlines.order_qty <> orderlines.despatched_qty
          )orderlines 
on orderheader.ordernumber= orderlines .ordernumber
WHERE orderlines.ordernumber is null

 

[gmmastros]

Try this:

Update OrderHeader
Set    Status = Coalesce(CompletedOrders.Status, 'E')
From   OrderHeader
       Left Join (
         Select OrderNumber, 'P' As Status
         From   OrderLines
         Group By OrderNumber
         Having Count(*) = Count(Case When Order_Qty = Despatched_Qty Then 1 End)
      ) As CompletedOrders
      On OrderHeader.OrderNumber = CompletedOrders.OrderNumber

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

 

[Jeniferous]

I've tried both the bottom options above (not sure i understood the first one!)

On both i get:
Line 5: Incorrect syntax near '.'.

Here's my actual code for both (I used examples to try explain myself earlier but here are my table names/fields)... In the second options i didn't quite understand what "completedorders" is as there isn't a completed orders table?

Essentially bs_SOP_despatch is the order headers and opdetm is the lines.

UPDATE dbo.bs_SOP_despatch
SET dbo.bs_SOP_despatch.[status]='P'
from dbo.bs_SOP_despatch
Left join (
Select Distinct scheme.opdetm.order_no
From scheme.opdetm
WHERE scheme.opdetm.order_qty <> scheme.opdetm.despatched_qty
) scheme.opdetm
on dbo.bs_SOP_despatch.sales_order = scheme.opdetm.order_no

and

Update dbo.bs_SOP_despatch
Set dbo.bs_SOP_despatch.[Status] = Coalesce(CompletedOrders.Status, 'E')
From dbo.bs_SOP_despatch
Left Join (
Select scheme.opdetm.order_no, 'P' As dbo.bs_SOP_despatch.[Status]
From scheme.opdetm
Group By order_no
Having Count(*) = Count(Case When scheme.opdetm.order_qty = scheme.opdetm.despatched_qty Then 1 End)
) As CompletedOrders
On dbo.bs_SOP_despatch.sales_order = CompletedOrders.OrderNumber

 

[jbenson001]

Completed orders in George's example is a derived table. If you look that the Left Join, he creates a table with in the "()" and gives it a name(alias) of CompletedOrders. He uses that name to join back to OrderHeader.

 

[gmmastros]

You made a mistake when you tried to convert my code to your table names. Try this:

Update dbo.bs_SOP_despatch
Set    dbo.bs_SOP_despatch.[Status] = Coalesce(CompletedOrders.Status, 'E')
From   dbo.bs_SOP_despatch
       Left Join ([blue]
         Select scheme.opdetm.order_no, 'P' As [Status]
         From   scheme.opdetm
         Group By order_no
         Having Count(*) = Count(Case When scheme.opdetm.order_qty = scheme.opdetm.despatched_qty Then 1 End)[/blue]
      ) As CompletedOrders
      On dbo.bs_SOP_despatch.sales_order = CompletedOrders.OrderNumber

Originally, you had it as:

Select scheme.opdetm.order_no, 'P' As [!]dbo.bs_SOP_despatch.[/!][Status]

I was using the 'P' as a hard coded value. The AS part is a column alias, so trying to put a table name in there was not the correct thing to do. Basically, my code is this...

The derived table part (highlighted in blue above) is meant to get those orders where all of the quantity = despatch. I then use this in a left join with the parent table. If the row exists, P will be returned from the derived table. If a matching row does not exist, NULL will be returned. This is why I use coalesce in the outer query to set it to the hard coded status column of the derived table or E if there is no matching row.

I know this may be a little tricky to understand. If there is any part of this that does not make complete sense to you, let me know and I will explain it.

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

 

[Jeniferous]

Few more tweaks and that worked great! Thanks... i almost understand it too! Just one last thing though - i need to exclude any where the scheme.opdetm.product column is NULL i.e. don't include them in the validation at all.

 

[Jeniferous]

I thought this was all working till I tested with live data.. what i wanted to do is set the header status to P if all the lines pass but if any line fails then set the header status to E. Here's my code... it's a slightly different check but the same theory as above.

Update bs_despatch
Set bs_despatch.validation_status = Coalesce(CompletedOrders.validation_status, 'E'),error_code= Coalesce(CompletedOrders.validation_status,'30')


From bs_despatch
Left Join (
Select product, order_no, 'P' As [validation_status]
From scheme.opdetm
Group By order_no, scheme.opdetm.product
Having Count(*) = Count(Case When scheme.opdetm.net_price>=0 Then 1 End) and RTRIM(scheme.opdetm.product) <> ''
) As CompletedOrders
On bs_despatch.sales_order = CompletedOrders.order_no
where bs_despatch.[status] <> 'A'or bs_despatch.validation_status <> 'E'

In reality its just ignore the lines which fail and setting everything to p?!

 

[drlex]

Your problem lies with the filter "where bs_despatch.[status] <> 'A' [red]or[/red] bs_despatch.validation_status <> 'E'"

The alternative "or" ensures that both statuses 'A' and 'E' will be selected. Try it with "and" in place of the "or" or change the code to "where bs_despatch.[status] not in ('A','E')"



soi là, soi carré

 

[Jeniferous]

Tried the above and it still marks the rogue order as Passed even though it has a line on where the net_price is 0.

 

[drlex]

Isn't that correct, given that your test is for zero and above?

...Case When scheme.opdetm.net_price[red][b]>=0[/b][/red] Then 1 End...

soi là, soi carré

 

[Jeniferous]

Probably not - i've tried a variety of options from <> to >= to <= to != but it still excludes the line with 0. I turned the above inot a select statment and it bought back everyline except the one with a 0... so its ignore it rather than setting the status E.