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
 General SQL Server Forums
 New to SQL Server Programming
 sql server 2005 update query not working anymore
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/08/2012 :  08:22:42  Show Profile  Reply with Quote
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.

Edited by - learning_grsql on 10/08/2012 08:23:54

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/08/2012 :  09:20:03  Show Profile  Reply with Quote
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 - 10/08/2012 :  16:40:24  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 10/08/2012 :  16:43:51  Show Profile  Visit chadmat's Homepage  Reply with Quote
Try updating stats.

-Chad
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/08/2012 :  16:45:09  Show Profile  Reply with Quote
@chadmat...which status?
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/08/2012 :  18:03:08  Show Profile  Visit chadmat's Homepage  Reply with Quote
Column/Index Statistics.

-Chad
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