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

General SQL Server Forums

New to SQL Server Programming

Update From

Author

Topic

jggtz
Starting Member

32 Posts

Posted - 2009-04-03 : 14:38:57

MS SQL Server 2005

Scenario :
1 table: Customers Fields: CustomerId, Name, Balance
1 table: Transactions Fields: CustomerId, SaleAmount

Best (and fast) way to update all the Customers with their Transactions

My intents for solutions are very slow
[Code]
UPDATE .dbo.Customers
SET .dbo.Customers.Balance=dbo.Customers.Balance+dbo.Tranasctions.SaleAmount
FROM .dbo.Customers
INNER JOIN dbo.Transactions ON.dbo.Customers.CustomerId=.dbo.Transactions.CustomerId
[/Code]

I'm lost!
Thanks
JG

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-03 : 14:46:49

The query looks good apart from some syntaxical mistakes. If its slow, you might want to check for indexing on Customers.CustomerId and Transactions.CustomerId.

jggtz
Starting Member

32 Posts

Posted - 2009-04-03 : 14:50:38

Thanks!
I'll check it
(Forgive my Syntax, was written on the fly)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:06:54

also no need of repeating table names everywhere, you can use short aliases like

UPDATE c
SET c.Balance=c.Balance+t.SaleAmount
FROM dbo.Customers c
INNER JOIN dbo.Transactions t
ON c.CustomerId=t.CustomerId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources