| Author |
 Topic  |
|
|
chuntley
Starting Member
8 Posts |
 Posted - 03/21/2002 : 11:23:56
|
I have an update query that joins across 3 tables. When the update query runs it updates a random number of rows but never the 8 million or so that it should. If I you the same joins and place a select * in front of it then it returns the number of rows I would expect. I have run DBCC DBReIndex on all the tables involved in this query but it still doesn't work Please see query examples below. The results are very odd and I suspect corruption but I am not sure how to fix it. If you notice my third example it has a simple "where t4.ta_id = t4.ta_id"
added to the end of the original query and this seems to make the query run. Help! =)
-- Does Not Work
update bi..ebpfload set phs_id = t4.phs_id
from bi..ebpfload t1 join ebdailyholdings t2 on t2.ta_id = t1.ta_id and t2.omniasset = t1.omniasset
join ebdailysource t3 on t3.ta_id = t1.ta_id and t3.source = t1.source
join ebholdingsource t4 on t1.ta_id = t4.ta_id and t2.sec_id = t4.sec_id and t3.source_id = t4.source_id
--Works
select t1.*,t4.phs_id
from bi..ebpfload t1 join ebdailyholdings t2 on t2.ta_id = t1.ta_id and t2.omniasset = t1.omniasset
join ebdailysource t3 on t3.ta_id = t1.ta_id and t3.source = t1.source
join ebholdingsource t4 on t1.ta_id = t4.ta_id and t2.sec_id = t4.sec_id and t3.source_id = t4.source_id
--Works
update bi..ebpfload set phs_id = t4.phs_id
from bi..ebpfload t1 join ebdailyholdings t2 on t2.ta_id = t1.ta_id and t2.omniasset = t1.omniasset
join ebdailysource t3 on t3.ta_id = t1.ta_id and t3.source = t1.source
join ebholdingsource t4 on t1.ta_id = t4.ta_id and t2.sec_id = t4.sec_id and t3.source_id = t4.source_id
where t4.ta_id = t4.ta_id
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2916 Posts |
Posted - 03/22/2002 : 06:28:23
|
any chance that not qualifying the "set phs_id = " with t1. would be a cause of the problem?
do the execution plans for all 3 queries (in particular the 2 updates) look similiar (or at least the differences make sense?)
|
 |
|
|
Teroman
Posting Yak Master
United Kingdom
115 Posts |
Posted - 03/22/2002 : 06:35:13
|
not sure if it'll fix you problem, but you might as well try it
instead of
update MyTable
set x = 1
from MyTable MT
....
try
update MT
set x = 1
from MyTable MT
...
I try to use the alias intead of the table again as you can get the table in the plan twice, and things can go a bit screwey.
col
|
|
|
|
jcelko
Esteemed SQL Purist
USA
547 Posts |
Posted - 03/22/2002 : 11:54:47
|
>> I have an update query that joins across 3 tables. ... <<
A UPDATE is a statement, not a query. And they do not JOIN anything; they change zero or more rows of a single base table. But that mistake in terminology shows that you have a conceptual model of SQL that is scarred by too much proprietary code.
Your statement ought to look like this:
UPDATE bi..ebpfload
SET phs_id
= (<<scalar query expression>>);
2) Did you mean to say "where t4.ta_id = t4.ta_i" ; unless there are some NULLs, this looks redundant...
3) Without any DDL, it is impossible to figure your columns references, keys, constraints and all that other stuff.
--CELKO--
Joe Celko, SQL Guru
|
|
|
|
chuntley
Starting Member
8 Posts |
Posted - 03/27/2002 : 16:07:36
|
I know the where t4.ta_id = t4.ta_id is redundant. However when this line is added to the end of the update "statement", it updates the number of rows I would expect. Without it, it updates a random number. Also there aren't any nulls in the join operation.
I went ahead and took the two previous suggestions and changed my query to look like this
update t1 set t1.phs_id = t4.phs_id
from bi..ebpfload t1 join ebdailyholdings t2 on t2.omniasset = t1.omniasset and t2.ta_id = t1.ta_id
join ebdailysource t3 on t3.source = t1.source and t3.ta_id = t1.ta_id
join ebholdingsource t4 on t2.sec_id = t4.sec_id and t3.source_id = t4.source_id and t2.ta_id = t4.ta_id
The Execution plans also show no real differences. The only ones that differ are the lines for the updates themselves which I imagine is because of the vast difference in the number of rows that it is updating.
Any additional suggestions would be greatly appreciated. Otherwise I may have to use the scalar approach.
Edited by - chuntley on 03/27/2002 16:24:59 |
|
|
| |
Topic |
|
Update Query Random Rows Affected Help!!