asked on

SUM in an UPDATE help

Am trying to update a temp table with data from a base table

TEMP has LOCATION, QTY

BASE has LOCATION,QTY,(additional Attributes that make location appear more than once)

I want to update my temp location with a sum of the QTY from the base tables

i.e my "wrong" update is like this

UPDATE temp
SET QTY=(SELECT SUM(qty) from BASE)
WHERE temp.location=base.location

BrandonGalderisi

UPDATE temp
SET QTY=(SELECT SUM(qty) from BASE WHERE temp.location=base.location)

Milleniumaire

Nearly, try:

UPDATE temp
SET QTY=(SELECT SUM(qty)
FROM BASE
WHERE temp.location=base.location)
WHERE temp.location IN (SELECT location
FROM base)

corpuslabs

I guess the below query might help u,

Update Temp set Qty = (Select Sum(Qty) from Base where Location = Temp.Location)

neal_harmon

ASKER

hmmm....getting error 3706: SYNTAX ERROR: EXPECTED SOMETHING BETWEEN '(' AND THE 'SELECT' KEYWORD

usually that's a missing paren or tick.....

i was try to simplify my original question, below is tha actual code - same logic though....

UPDATE seven_day_tables.nh_cvv1
SET a0108=(SELECT SUM(nbr_lines) FROM vbus.acct_v12 WHERE seven_day_tables.nh_cvv1.clac_cd=vbus.acct_v12.clac_cd)
WHERE seven_day_tables.nh_cvv1.clac_cd IN (SELECT clac_cd FROM vbus.acct_v12);

BrandonGalderisi

What DB engine?

neal_harmon

ASKER

teradata

ASKER CERTIFIED SOLUTION

corpuslabs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial