Update Query Random Rows Affected Help!!

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

SQL Server Development (2000)

Update Query Random Rows Affected Help!!

Author

Topic

chuntley
Starting Member

8 Posts

Posted - 2002-03-21 : 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
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-22 : 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

115 Posts

Posted - 2002-03-22 : 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

547 Posts

Posted - 2002-03-22 : 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 - 2002-03-27 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources