Update ..From - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update ..From

Author

Topic

psfaro
Starting Member

49 Posts

Posted - 2014-05-05 : 06:23:21

Hi ,

Table #t1:

cConta cod_ctc vcr1
7 90000001 3711137.28
7 90000001 856110.79

Table #t2

cod_conta cod_ctc vcr1
7 90000001 0.00

I need to update Vcr1 with the 2 values of #t1 table (cconta=7)

UPDATE #t2
set VCR1 +=b.vcr1
FROM #t1 b INNER JOIN #t2 a ON a.cod_conta=b.cconta
AND a.cod_ctc=b.cod_ctc

It only update 1 Record

cod_conta cod_ctc vcr1
7 90000001 3711137.28

How can Update #T2 with the total of the 2 Values in #t1

Regards

Pedro Faro

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-05 : 06:33:23

not tested...




UPDATE A
SET VCR1=B.sumVcr1
FROM	
	@t2 as A
	INNER JOIN 
	(
		select
			sum(vcr1) as sumVcr1
			,cConta
			,cod_ctc
		From 
			Table#t1
		Group by 
			cConta
			,cod_ctc
	) B
	ON A.cod_conta=B.cConta
	AND a.cod_ctc=b.cod_ctc

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-05 : 06:37:15

[code]

UPDATE A
SET VCR1=B.sumVcr1
FROM
#t2 as A
INNER JOIN
(
select
sum(vcr1) as sumVcr1
,cConta
,cod_ctc
From
#t1
Group by
cConta
,cod_ctc
) B
ON A.cod_conta=B.cConta
AND a.cod_ctc=b.cod_ctc
[/code]

and the output:
[code]
cod_conta cod_ctc vcr1
7 90000001 4567248.07
[/code]

sabinWeb MCP

psfaro
Starting Member

49 Posts

Posted - 2014-05-05 : 06:38:03

Hi,
Tanks for response , but i need a "simple code" if possible :-)

In my example

UPDATE #t2
set VCR1 +=b.vcr1
FROM #t1 b INNER JOIN #t2 a ON a.cod_conta=b.cconta
AND a.cod_ctc=b.cod_ctc

But i have several values to Update

set VCR1 +=b.vcr1 ,
Vcr2 +=b.vcr2,
Vcr3 +=b.vcr3 ..

Regards

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-05 : 06:48:16

you can use CTE, like this:



;with cteT1
AS
	(
		select 
			cConta
			,cod_ctc
			,sum(vcr1) as sumVCR1
			,sum(vcr2) as sumVCR2
		from 
			#t1
		Group by 
			cConta
			,cod_ctc
)

UPDATE A
SET A.VCR1= B.sumVcr1 
	,A.VCR2=  B.sumVcr2
FROM	
	#t2 as A
	INNER JOIN 
	cteT1 as B
	ON A.cod_conta=B.cConta
	AND a.cod_ctc=b.cod_ctc

sabinWeb MCP

psfaro
Starting Member

49 Posts

Posted - 2014-05-05 : 07:18:21

Hi stepson

Tanxs for your response .

I will make a Acummulated of the values on #T1 table previous to update #t2 ..

I thought that Update "from source" would update #T2 table só many records in #t1 , mas it seems that make 1 time only.

Regards

Pedro

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources