SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update ..From
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

psfaro
Starting Member

Portugal
49 Posts

Posted - 05/05/2014 :  06:23:21  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
435 Posts

Posted - 05/05/2014 :  06:33:23  Show Profile  Reply with Quote
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
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
435 Posts

Posted - 05/05/2014 :  06:37:15  Show Profile  Reply with Quote


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 


and the output:

cod_conta	cod_ctc	vcr1
7	90000001	4567248.07




sabinWeb MCP
Go to Top of Page

psfaro
Starting Member

Portugal
49 Posts

Posted - 05/05/2014 :  06:38:03  Show Profile  Reply with Quote
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
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
435 Posts

Posted - 05/05/2014 :  06:48:16  Show Profile  Reply with Quote
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
Go to Top of Page

psfaro
Starting Member

Portugal
49 Posts

Posted - 05/05/2014 :  07:18:21  Show Profile  Reply with Quote
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

Edited by - psfaro on 05/05/2014 07:19:18
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000