update from select sum on 2 fields

I need to update a table with a select sum on 2 fields in another table.

For example, I need to update  col1 of  table #temp with sum of total from table1,
update col2 of table #temp with sum of cash from table1.

I have no idea how to script this, but maybe this will give you an idea of what I need.

update #temp
set col1 =
col2=
select sum(total), sum(cash)
from table 
where #temp.id = 1

Open in new window

Delta7428Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Does either table have a common column, such as an id field?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Assuming yes, replace the id with the common column name, and give this a whirl.
UPDATE #tmp
SET Col1 = s.sum_total, Col2 = s.sum_cash
FROM #tmp t
   JOIN (SELECT id, SUM(total) as sum_total, SUM(cash) as sum_cash FROM SomeTable GROUP BY id) s ON t.id = s.id

Open in new window

0
Ron MalmsteadInformation Services ManagerCommented:
Seems simple enough ..

Update #temp
Set col1=(select sum(total) from table1),
Col2=(select sum(cash) from table1)
Where [id]=1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Delta7428Author Commented:
There is no common id.

I am creating the temp table, inserting a record, then need to update the same record (id=1).
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In that case you can use xuserx2000's example of an pair of uncorrelated subqueries updating two columns.
0
Delta7428Author Commented:
I didn't see xuserx2000's example until after I posted.

Here is my actual query with attempt to modify to xuserx2000's example.  Maybe I oversimplified it in my example.  I am getting "Incorrect syntax near the keyword where"

update #Summary set
		
		GO_Renewal = (Select sum(TotalNote),
		NET_Renewal = (Select Sum(CashToCustomer)
			from contracts c
			inner join names n 
			on c.branchid = n.branchid 
			and c.namesid = n.namesid and c.branchid = n.branchid
			where
			 c.totaloldbalance <> 0
			and c.processstatus = 3
			and c.loandate between @Start and @End
where #Summary.id = 1

Open in new window

0
Ron MalmsteadInformation Services ManagerCommented:
If there is only one record.. You don't need the where clause at all.
0
Ron MalmsteadInformation Services ManagerCommented:
I don't see a declare statement for these

"@Start and @End"

Also you have multiple joins on the same fields for the same two tables.. Which that is likely the offending line.
0
Ron MalmsteadInformation Services ManagerCommented:
Only one join should be needed unless you have an additional query on the same table to add to this..
                  from contracts c
                  inner join names n
                  on c.branchid = n.branchid
0
Delta7428Author Commented:
I am not showing the declare and set for @start and @end for the sake of brevity.

The same select statement with the same joins.  I am trying to modify it into an update statement.  The joins work fine in this select:
insert into #Summary
            (
            GO_Renewal,
            NET_Renewal
            )
            Select sum(TotalNote) as GO_Renewal ,
            Sum(CashToCustomer) as NET_Renewal

                  from contracts c
                  inner join names n
                  on c.branchid = n.branchid
                  and c.namesid = n.namesid and c.branchid = n.branchid
                  where
                   totaloldbalance <> 0
                  and processstatus = 3
                  and loandate between @Start and @End
0
Delta7428Author Commented:
Here is the declare and set:
declare @Start                                    smalldatetime
declare @End                                    smalldatetime
set @Start = '8/1/13'
set @End = '8/31/13'
0
Delta7428Author Commented:
I didn't read your post thoroughly.  I removed the duplicate joins.  Select still works.  Same issue with update.
insert into #Summary
            (
            GO_Renewal,
            NET_Renewal
            )
            Select sum(TotalNote) as GO_Renewal ,
            Sum(CashToCustomer) as NET_Renewal

                  from contracts c
                  inner join names n
                  on c.branchid = n.branchid
                  and c.namesid = n.namesid
                  where
                   totaloldbalance <> 0
                  and processstatus = 3
                  and loandate between @Start and @End
0
Delta7428Author Commented:
I meant "insert still works" , instead of "select still works".
0
Ron MalmsteadInformation Services ManagerCommented:
Use the same selects but separately in the update statement .

Post the update statement you have now
0
Delta7428Author Commented:
There is only 1 record.  Not sure why I threw in the last where clause.

I am getting now "Incorrect syntax near @End
insert into #Summary
		(
		GO_Renewal,
		NET_Renewal
		)
		Select sum(TotalNote) as GO_Renewal , 
		Sum(CashToCustomer) as NET_Renewal

			from contracts c
			inner join names n 
			on c.branchid = n.branchid 
			and c.namesid = n.namesid 
			where
			 totaloldbalance <> 0
			and processstatus = 3
			and loandate between @Start and @End

Open in new window

0
Delta7428Author Commented:
copied and pasted the insert by mistake.. 1 min
0
Delta7428Author Commented:
insert into #Summary
            (
            GO_Renewal,
            NET_Renewal
            )
            Select sum(TotalNote) as GO_Renewal ,
            Sum(CashToCustomer) as NET_Renewal

                  from contracts c
                  inner join names n
                  on c.branchid = n.branchid
                  and c.namesid = n.namesid
                  where
                   totaloldbalance <> 0
                  and processstatus = 3
                  and loandate between @Start and @End
0
Delta7428Author Commented:
Geeeze, clipboard issue... Here is update
update #Summary set
            
            GO_Renewal = (Select sum(TotalNote),
            NET_Renewal = (Select Sum(CashToCustomer)

                  from contracts c
                  inner join names n
                  on c.branchid = n.branchid
                  and c.namesid = n.namesid
                  where
                   c.totaloldbalance <> 0
                  and c.processstatus = 3
                  and c.loandate between @Start and @End
0
Delta7428Author Commented:
Added closing parens.  Now getting "aggregate may not appear in the set list of an update statement.
update #Summary set
            
            GO_Renewal = (Select sum(TotalNote)),
            NET_Renewal = (Select Sum(CashToCustomer))

                  from contracts c
                  inner join names n
                  on c.branchid = n.branchid
                  and c.namesid = n.namesid
                  where
                   c.totaloldbalance <> 0
                  and c.processstatus = 3
                  and c.loandate between @Start and @End
0
Delta7428Author Commented:
I found the solution of sqlteam.com.   xuserx2000, if you want to copy and paste into another post, I'll accept.  Sorry for my scatterbrain errors in posting.  It's been a long week.  Here's solution:
update #Summary set
		GO_Renewal = a.SumGO,
		NET_Renewal = a.SumNet
		from
		(
		Select sum(c.TotalNote) as sumGO,
		Sum(c.CashToCustomer) as sumNET

			from contracts c
			inner join names n 
			on c.branchid = n.branchid 
			and c.namesid = n.namesid 
			where
			 totaloldbalance <> 0
			and processstatus = 3
			and loandate between @Start and @End
			) a

Open in new window

0
Ron MalmsteadInformation Services ManagerCommented:
As long as you got it to work that's good enough for me.

Cheers
0
Delta7428Author Commented:
How do I close the question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.