November 9, 2010 at 2:52 pm
Hi:
Consider the following UPDATE statement, where the values of the updated table columns depend on the values of another (reference) table's columns....
[font="Courier New"]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 [/font]
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.
November 9, 2010 at 3:53 pm
The last row found in the reference table is the last value entered/updated into the target table.
An easy test:
CREATE TABLE #tmp (ID1 INT, Val1 VARCHAR(10))
CREATE TABLE #tmp2 (ID2 INT, Val2 VARCHAR(10))
INSERT INTO #tmp (ID1, Val1) VALUES ( 1, 'abc')
INSERT INTO #tmp (ID1, Val1) VALUES ( 1, 'def')
INSERT INTO #tmp2 (ID2, Val2) VALUES ( 1, 'aaa')
INSERT INTO #tmp2 (ID2, Val2) VALUES ( 1, 'bbb')
INSERT INTO #tmp2 (ID2, Val2) VALUES ( 1, 'ccc')
INSERT INTO #tmp2 (ID2, Val2) VALUES ( 1, 'ddd')
UPDATE t1
SET Val1 = t2.Val2
FROM #tmp AS t1
JOIN #tmp2 AS t2
ON t1.ID1 = t2.ID2
SELECT * FROM #tmp
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply