When my SELECT statment works and my UPDATE won't

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

Transact-SQL (2000)

When my SELECT statment works and my UPDATE won't

Author

Topic

chuntley
Starting Member

8 Posts

Posted - 2002-03-21 : 12:44:58

I have an update query that joins across 4 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

Jay99

468 Posts

Posted - 2002-03-21 : 12:48:16

Please don't cross post.

I read your post the first time and I am still scratching my head . . . can you replicate this occurance with some DML from the Northwinds or Pubs database so I can run it? At first blush, I don't see anything wrong with your code.

Jay
<O>

chuntley
Starting Member

8 Posts

Posted - 2002-03-21 : 12:57:16

Sorry for the cross post I a newb and didn't know it was frowned upon here. I will look at northwind and see if I can dup this issue. I am not sure if it will be possible but I'll give it a go.

chuntley
Starting Member

8 Posts

Posted - 2002-03-21 : 13:13:51

Well after thinking about it. There really isn't anyway that I can duplicate this in northwind. I didn't mention it before but this query ran fine for about 1 year now and then all of a sudden stopped. Which is another reason I suspect corruption. Plus I find it odd the this update works when I add the dummy where clause to it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-21 : 13:21:13

Run DBCC CHECKTABLE or DBCC CHECKDB and see if it reports any errors. If you get errors, run them again using the REPAIR_REBUILD option. If there is any actual corruption this should fix it (DBREINDEX doesn't fix corrupted tables).

After that's done, try UPDATE STATISTICS on the table and see if it improves the performance. You might want to run these operations when the database not busy, because they will take some time (and you need to set the database to single user mode for the DBCC CHECKs)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources