Update Query

[mairving]

I am trying to update a numeric field in a table. It is a quantity on hand(Decimal) column. Basically I would have:
An indexed field - itemid
A location_id
The quantity field.
This query works:

UPDATE tablename
set [quantity]
= (SELECT [quantity] as qoh from inv_loc where [itemid]= 'value' and location_id=value) + (SELECT [quantity] as qoh from inv_loc where [itemid]= 'value' and location_id=other_value)
where itemid=itemid
and location_id=value)


What I want to do is to take all the quantities that are tied to one location_id and add those values to the other quantity for the other location_id. Pretty easy to do with one value but I am not sure how best to do it with multiple values.

Thanks for your help.

 

[ESquared]

First of all the query you show doesn't work because it has an extraneous trailing parenthesis. Second your column names don't specify what table they come from so it's nearly impossible to tell which table you might mean

itemid = itemid

Second, do you realize that all this expression does is eliminate null itemids? itemid when not null will always equal itemid. Unless they're from different tables, but your query doesn't say.

You'd best give some example data with column names and final result before we can really help you.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[mairving]

ESquared, Thanks.

Yep I see the extra ). Okay here are some examples if that will clarify things.

Table - Inventory
(itemid 1)
itemid = 1
locationid = 1
qty = 2

itemid = 1
locationid = 2
qty = 1

Table - Inventory
(itemid 2)
itemid = 2
locationid = 1
qty = 5

itemid = 2
locationid = 2
qty = 3

What I want to do is on the first item add the qty so
that
itemid 1 qty = 2 + 1 =3 for locationid 1
and
itemid 2 qty = 5 + 3 =8 for locationid 1

It is pretty easy to do this for one value but what I would like to do is to update all itemid's by adding the 2 values.

Does that make better sense?

 

[SQLSister]

try using a self join. Something like:

update a
set qty = a.qty + b.qty
--select a.qty, a.qty + b.qty, a.locationid, b.locationid 
from table1 a 
join table2 b on a.itemid = b.itemid
where a.locationid  = 'test'
and b.locationid = 'test2'

Test using the select to make sure it is doing what you want before running.


"NOTHING is more important in a database than integrity." ESquared