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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
Aged Yak Warrior

Romania
545 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
Aged Yak Warrior

Romania
545 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
Aged Yak Warrior

Romania
545 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  
 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.05 seconds. Powered By: Snitz Forums 2000