[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 773
  • Last Modified:

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

0
neal_harmon
Asked:
neal_harmon
  • 2
  • 2
  • 2
  • +1
1 Solution
 
BrandonGalderisiCommented:
UPDATE temp
SET QTY=(SELECT SUM(qty) from BASE WHERE temp.location=base.location)

0
 
MilleniumaireCommented:
Nearly, try:

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

I guess the below query might help u,

Update Temp set Qty = (Select Sum(Qty) from Base where Location = Temp.Location)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
neal_harmonAuthor 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);
0
 
BrandonGalderisiCommented:
What DB engine?
0
 
neal_harmonAuthor Commented:
teradata
0
 
corpuslabsCommented:
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);
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now