We help IT Professionals succeed at work.

SUM in an UPDATE help

neal_harmon
neal_harmon asked
on
Medium Priority
785 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

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

Nearly, try:

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

I guess the below query might help u,

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

Author

Commented:
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);
What DB engine?

Author

Commented:
teradata
Try this out ..,


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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.