Behavior of UPDATE With Reference Table

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 2005 Forums

Transact-SQL (2005)

Behavior of UPDATE With Reference Table

Author

Topic

bad_shogun
Starting Member

1 Post

Posted - 2010-11-09 : 17:02:23

Hi:

Consider the following UPDATE statement, where the values of the updated table columns depend on the values of another (reference) table's columns....

UPDATE a
SET a.FADailyDivRate = b.DailyDividendRate,
a.FANetAsset = b.NetAssets,
a.FADailyYield = b.DayYield1,
a.FAWeekYield = b.DayYield7,
a.FAMonYield = b.DayYield30
FROM tbl_fund_history a
JOIN tbl_l1_fund_accounting b
ON a.FundId = b.FundId
AND a.DataDate = b.DataDate
WHERE b.NetAssets IS NOT NULL

The JOIN condition does not constitute a primary key for the updated table. There are multiple rows in the updated table whose FundId and DataDate values satisfy the JOIN condition. The same is true of the other table. This is update runs without error.

Can anyone tell me which values the update table columns are set to when more than one row in the other table is returned from the nested query?

Thanks.

--------------------
Tip: Don't eat yellow snow.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-09 : 17:10:12

>>Can anyone tell me which values the update table columns are set to when more than one row in the other table is returned from the nested query?
I don't see a nested query in your example. But to answer your question:

In a way, all of them. You only see the effects of the last one. Generally speaking you can't reliably predict/know which of the rows is the final updated value.

EDIT:
one way to insure that you get just one particular row as your source values to update for a given (FundID,DataDate) is with this technique. You just need to define the ORDER BY to whatever makes sense:


UPDATE a SET 
	a.FADailyDivRate = ca.DailyDividendRate,
	a.FANetAsset = ca.NetAssets,
	a.FADailyYield = ca.DayYield1,
	a.FAWeekYield = ca.DayYield7,
	a.FAMonYield = ca.DayYield30
FROM	tbl_fund_history a
cross apply (
	select	top 1 
		b.DailyDividendRate,
		b.NetAssets,
		b.DayYield1,
		b.DayYield7,
		b.DayYield30
	from	tbl_l1_fund_accounting  b
	where	b.FundID = a.FundID
	and	b.DataDate = a.DataDate
	and	b.NetAssets is not null
	ORDER BY <SOME COLUMN(s)>
	) ca

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources