Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

update products.inwarehouse1 orderitems by the sum total of orderitems.is1q+orderitems.is2q+orderitems.is3q (int)

select * from orderitems i
left join products p on p.productid=i.productid
where i.itemid>400000 order by i.itemid desc


I want to update products.ourinwarehouse1  (int)
by the sum total of
orderitems.is1q+orderitems.is2q+orderitems.is3q   (int)


but what if orderitems.is3q is null
0
rgb192
Asked:
rgb192
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will do:
update p
  set ourinwarehouse1  = ( select sum(is1q + is2q + is3q) from orderitems oi where oi.productid = p.productid and oi.itemid > 400000 )
  from products p

Open in new window

0
 
rgb192Author Commented:
and if any of those values are null

do i need to do
isnull(is1q,0)
0
 
mquirozCommented:
something like:

update products.inwarehouse1 set qtycolumn = isnull(t2.qty, 0) + isnull(t3.qty, 0)
from products.inwarehouse1 t1
left outer join products.inwarehouse2 t2  on  t1.id = t2.id
left outer join products.inwarehouse3 t3  on  t2.id = t3.id
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes
0
 
rgb192Author Commented:
mquiroz
I dont understand


angelIII

I only want to update one oi.itemid to test
i tried

update p
  set ourinwarehouse1  = ( select sum(oi.is1q + oi.is1n+p.ourinwarehouse1) from orderitems oi where oi.productid = p.productid and oi.itemid = 406438 )
  from products p

Msg 8124, Level 16, State 1, Line 1
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.
0
 
mquirozCommented:
you can use a join to update the table
0
 
jagssiduralaCommented:
update      products
set            ourinwarehouse1  = A.total
(      
      select      Sum(Isnull(is1Q, 0) + Isnull(is2Q, 0) + Isnull(is3Q, 0)) as total,
                  Oi.productId
      From      orderitems oi
      where      oi.productid = p.productid
      And            oi.itemid > 400000
      Group
      By            oi.ProductId
)A
Where productId = OI.ProductId
0
 
rgb192Author Commented:
thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now