SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Update Query Random Rows Affected Help!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chuntley
Starting Member

8 Posts

Posted - 03/21/2002 :  11:23:56  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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?)

Go to Top of Page

Teroman
Posting Yak Master

United Kingdom
115 Posts

Posted - 03/22/2002 :  06:35:13  Show Profile  Send Teroman an AOL message  Reply with Quote
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

Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 03/22/2002 :  11:54:47  Show Profile  Visit jcelko's Homepage  Reply with Quote
>> 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
Go to Top of Page

chuntley
Starting Member

8 Posts

Posted - 03/27/2002 :  16:07:36  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000