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
  • 3
  • 2
  • 2
  • +1
LVL 142

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 =
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33720443
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dataset not reading table data 12 46
SSIS how to COMPARE a data column from different servers? 6 89
CONVERT date time to a different time zone. 2 45
Need help with a query 6 67
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

896 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now