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

Posted on 2010-09-20
Last Modified: 2012-05-10
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
Question by:rgb192
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33720403
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


Author Comment

ID: 33720424
and if any of those values are null

do i need to do

Expert Comment

ID: 33720431
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 =
left outer join products.inwarehouse3 t3  on =
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33720443

Author Comment

ID: 33720555
I dont understand


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.

Expert Comment

ID: 33730221
you can use a join to update the table
LVL 12

Accepted Solution

jagssidurala earned 500 total points
ID: 33732251
update      products
set            ourinwarehouse1  =
      select      Sum(Isnull(is1Q, 0) + Isnull(is2Q, 0) + Isnull(is3Q, 0)) as total,
      From      orderitems oi
      where      oi.productid = p.productid
      And            oi.itemid > 400000
      By            oi.ProductId
Where productId = OI.ProductId

Author Closing Comment

ID: 33846218

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL ( and MongoDB (…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question