SQL Server Forums - sql server 2005 update query not working anymore

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

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

Topic

learning_grsql
Posting Yak Master

165 Posts

Posted - 10/08/2012 : 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.

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

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts

Posted - 10/08/2012 : 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

learning_grsql
Posting Yak Master

165 Posts

Posted - 10/08/2012 : 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.

chadmat
The Chadinator

USA
1962 Posts

Posted - 10/08/2012 : 16:43:51