rgb192
asked on
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+orde ritems.is3 q (int)
but what if orderitems.is3q is null
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
but what if orderitems.is3q is null
ASKER
and if any of those values are null
do i need to do
isnull(is1q,0)
do i need to do
isnull(is1q,0)
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
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
yes
ASKER
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.
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 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.
you can use a join to update the table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
Open in new window