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
 General SQL Server Forums
 New to SQL Server Programming
 sql server 2005 update query not working anymore

Author  Topic 

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-08 : 08:22:42
HI,

I have a simple update query similar to below. It was a working query but since yesterday it has stopped and I don't know how to get it back work.

When i run it, it keeps on showing "executing" but it doesn't execute at all.


update set table1.field2 = table2.field2 from table1
inner join table2
on table1.field3 = table2.field3
where date between '20120801' and '20120831'


Even before I had such an issue but usually when i restart sql server service, it used to work but this time it failed to work, no matter what I try.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-08 : 09:20:03
I am assuming that the syntax error in your posting is a typo. If not, you need an extra table1 in the update statement; see in red below:
UPDATE table1
SET table1.field2 = table2.field2
FROM table1
INNER JOIN table2
ON table1.field3 = table2.field3
WHERE date BETWEEN '20120801' AND '20120831'
Run each of the statements below to see what might be wrong. In the sp_who2, you are looking for BlkBy column to see if there are any processes blocking the update query
-- 1
SELECT TOP 10 field2,field3
FROM table1
INNER JOIN table2
ON table1.field3 = table2.field3
WHERE date BETWEEN '20120801' AND '20120831'

-- 2
SELECT COUNT(*)
FROM table1
INNER JOIN table2
ON table1.field3 = table2.field3
WHERE date BETWEEN '20120801' AND '20120831'

-- 3
sp_who2
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-08 : 16:40:24
The first two your queries has no issue. It works fine and displays results in few seconds.

when I execute my query, it takes ages to execute. It took 18 minutes to execute the update query. But I'm sure the same query worked and executed in few seconds till two days before and even when it stuck occasionally, if I restart sql server or computer, it used to work fine.

But when I see sp_who2 result while running my update query, it shows nothing in BlkBy Column but I notice it shows four rows each time while I run update query.


Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-08 : 16:43:51
Try updating stats.

-Chad
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-10-08 : 16:45:09
@chadmat...which status?
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-08 : 18:03:08
Column/Index Statistics.

-Chad
Go to Top of Page
   

- Advertisement -