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

Posted on 2010-09-20
Medium Priority
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  t1.id = t2.id
left outer join products.inwarehouse3 t3  on  t2.id = t3.id
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

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 2000 total points
ID: 33732251
update      products
set            ourinwarehouse1  = A.total
      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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 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