update statement with CTE select

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 statement with CTE select

Author

Topic

anthor
Starting Member

14 Posts

Posted - 2012-08-31 : 15:22:59

DECLARE @Tbl_Account TABLE(
AccNo int,
Add_By_AccNo int,
Comm decimal(18,8),
lvl nvarchar(50),
Balance decimal(18,3))
INSERT @Tbl_Account VALUES(2,0,6,'MANAGER',500),(3,2,4,'SUPERVISOR',1000),(5,3,3,'SALE PERSON',200),(6,2,4,'SUPERVISOR',100)

DECLARE @Tbl_Transaction TABLE(
Transaction_AccNo int,
Comm decimal(18,8),
Amount decimal(18,8))

DECLARE @PurchasePrice decimal(18,3)
set @PurchasePrice = 10.00;

WITH Dependencies AS(
SELECT * FROM @Tbl_Account WHERE AccNo = 5 UNION ALL
SELECT t.* FROM @Tbl_Account t
JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo)
SELECT @PurchasePrice as Amount,d1.Comm-ISNULL((SELECT Comm FROM Dependencies WHERE Add_By_AccNo=d1.AccNo),0) as Comm,d1.AccNo FROM Dependencies d1 LEFT JOIN Dependencies d2 ON d1.Add_By_AccNo = d2.AccNo

this is the CTE result
Amount Comm AccNo
10.000 3.00000000 5
10.000 1.00000000 3
10.000 2.00000000 2

Question:
1)in Tbl_Account Table i would like update the balance with this CTE result's Comm Column.Example: in Tbl_Account AccNo equal 5's Balance Plus $3,and AccNO Equal 3's Balance Plus $1 and AccNo equal 2 Plus $2.

how to do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 21:08:57

the last part of your query. Not sure why you have a LEFT JOIN there. I mask if off


update	t
set	Balance	= t.Balance + d1.Comm - ISNULL((SELECT Comm FROM Dependencies WHERE Add_By_AccNo = d1.AccNo),0)
FROM 	Dependencies d1 
-- 	LEFT JOIN Dependencies d2 	ON 	d1.Add_By_AccNo = d2.AccNo
	inner join @Tbl_Account t	on	d1.AccNo	= t.AccNo

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources