asked on

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

Jim Horn

Does either table have a common column, such as an id field?

Jim Horn

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

ASKER CERTIFIED SOLUTION

Ron Malmstead

membership

This solution is only available to members.

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

Start Free Trial

Dee

ASKER

There is no common id.

I am creating the temp table, inserting a record, then need to update the same record (id=1).

Jim Horn

In that case you can use xuserx2000's example of an pair of uncorrelated subqueries updating two columns.

Dee

ASKER

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

Ron Malmstead

If there is only one record.. You don't need the where clause at all.

Ron Malmstead

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.

Ron Malmstead

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

Dee

ASKER

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

Dee

ASKER

Here is the declare and set:

declare @Start smalldatetime
declare @End smalldatetime
set @Start = '8/1/13'
set @End = '8/31/13'

Dee

ASKER

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

Dee

ASKER

I meant "insert still works" , instead of "select still works".

Ron Malmstead

Use the same selects but separately in the update statement .

Post the update statement you have now

Dee

ASKER

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

Dee

ASKER

copied and pasted the insert by mistake.. 1 min

Dee

ASKER

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

Dee

ASKER

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

Dee

ASKER

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

Dee

ASKER

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

Ron Malmstead

As long as you got it to work that's good enough for me.

Cheers

Dee

ASKER

How do I close the question?