Link to home
Create AccountLog in
Avatar of AbeSpain
AbeSpainFlag for United Kingdom of Great Britain and Northern Ireland

asked on

T-SQL Updates and Aliases

I have a table I need to update but need to reference the primary key identity of the row that is being update in the update part of the status. I know you can't set an alias in T-Sql for the table being updated. In the following statement, tblO would be the reference to the table being update but I don't know how to feed this in to the statement.


Begin
	Set NoCount On
		Update 
			tblOrder
		Set
			OrderTotalFixed = 
		Coalesce(
		Round((
		Select 
			Sum(ProductVariationTotalCost * (1 - Coalesce(PromotionsPercentage,0))) 
		FROM         
			tblOrderProduct INNER JOIN
			tblOrder ON tblOrderProduct.OrderiD = tblOrder.OrderiD
		Where
			tblOrderProduct.OrderiD = tblO.OrderiD
		And 
			ProductVariationNoDiscount = 1 
		),2),0)

		+

		Coalesce(
		Round((
		Select 
			Sum(ProductVariationTotalCost) 
		FROM         
			tblOrderProduct INNER JOIN
			tblOrder ON tblOrderProduct.OrderiD = tblOrder.OrderiD
		Where
			tblOrderProduct.OrderiD = tblO.OrderiD
		And 
			ProductVariationNoDiscount = 2
		),2),0)	
		
		Where
			OrderStatusid = @OrderStatusiD
		And
			OrderTotalFixed Is Null
	
	Set NoCount Off
End

Open in new window

Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

have you tried to do your JOINs outside the SET clause?

Update O

Set OrderTotalFixed = Coalesce(Round((P1.ProductVariationTotalCost),2),0) + Coalesce(Round((P2.ProductVariationTotalCost),2),0)

FROM tblOrder as O

inner join (
      select OrderiD, sum(ProductVariationTotalCost) as ProductVariationTotalCost
      from tblOrderProduct
      WHERE ProductVariationNoDiscount = 1
      group by OrderiD
) AS P1
ON P1.OrderiD = O.OrderiD

inner join (
      select OrderiD, sum(ProductVariationTotalCost) as ProductVariationTotalCost
      from tblOrderProduct
      WHERE ProductVariationNoDiscount = 2
      group by OrderiD
) AS P2
ON P2.OrderiD = O.OrderiD

Where O.OrderStatusid = @OrderStatusiD
And O.OrderTotalFixed Is Null
You could set an alias to the table that is being updated. I think this is what you are looking for

update x set x.OrderTotalFixed = Coalesce(Round((y.sum1),2),0)
from tblOrder x
      INNER JOIN
                  (select OrderiD, Sum(case ProductVariationNoDiscount when 1 then ProductVariationTotalCost * (1 - Coalesce(PromotionsPercentage,0))
                                                                                                      else ProductVariationTotalCost end) Sum1
                  from tblOrderProduct where ProductVariationNoDiscount in (1,2) group by OrderiD) y ON x.OrderiD = y.OrderiD

>I know you can't set an alias in T-Sql for the table being updated
 that's incorrect

Correction

update x set x.OrderTotalFixed = Coalesce(Round((y.sum1),2),0)
from tblOrder x
      INNER JOIN
                  (select OrderiD, Sum(case ProductVariationNoDiscount when 1 then ProductVariationTotalCost * (1 - Coalesce(PromotionsPercentage,0))
                                                                                                      else ProductVariationTotalCost end) Sum1
                  from tblOrderProduct where ProductVariationNoDiscount in (1,2) group by OrderiD) y ON x.OrderiD = y.OrderiD and x.OrderTotalFixed Is Null
Avatar of AbeSpain

ASKER

Sachitjain, that doesn't work because PromotionsPercentage isn't a column in tblOrderProduct.
slight modification to my query:

Update O

Set OrderTotalFixed = Coalesce(Round((P1.ProductVariationTotalCost),2),0) + Coalesce(Round((P2.ProductVariationTotalCost),2),0)

FROM tblOrder as O

inner join (
      select OrderiD, Sum(ProductVariationTotalCost * (1 - Coalesce(PromotionsPercentage,0))) as ProductVariationTotalCost
      from tblOrderProduct
      WHERE ProductVariationNoDiscount = 1
      group by OrderiD
) AS P1
ON P1.OrderiD = O.OrderiD

inner join (
      select OrderiD, sum(ProductVariationTotalCost) as ProductVariationTotalCost
      from tblOrderProduct
      WHERE ProductVariationNoDiscount = 2
      group by OrderiD
) AS P2
ON P2.OrderiD = O.OrderiD

Where O.OrderStatusid = @OrderStatusiD
And O.OrderTotalFixed Is Null
As above, promotionspercentage is a column on tblOrder so these are failing due to that.
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Ok, that works but oddly, not all the rows get updated; only 5 out of 201 rows actually updated.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Ok, that works. I need to look at this code look how it is made up and implement elsewhere :)
Thank you, very appreciated
Try this then

update z set z.OrderTotalFixed = k.Sum1
from tblOrder z inner join  
      (select x.OrderiD OrderId, Coalesce(Round((Sum(case x.ProductVariationNoDiscount when 1
                              then ProductVariationTotalCost * (1 - Coalesce(PromotionsPercentage,0))
                                        else ProductVariationTotalCost end)),2),0) Sum1
      from tblOrder x INNER JOIN tblOrderProduct y on x.orderid = y.orderid
      where x.ProductVariationNoDiscount in (1,2)
      group by x.OrderiD) k inner join on x.orderid = k.orderid
where k.OrderTotalFixed Is Null