Query runs, but never finishes....

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

Query runs, but never finishes....

Author

Topic

offspring22
Starting Member

38 Posts

Posted - 2009-11-10 : 13:01:53

Hello,

So I have the query below, to update 1 table and change 1 field to the contents of the same field on a different table, as seen below.

update userlist
set public_id = b.public_ID
from userlist a, member.dbo.members b
where a.board_ID = b.member_number
and a.board_ID is not NULL

It appears to run, but I can let it sit for minutes and it never finishes or changes anything. Neither table is huge, just ~6000 rows on each. Anything I'm doing wrong here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-10 : 13:04:36

[code]update a
set a.public_id = b.public_ID
from userlist a
inner join member.dbo.members b
on a.board_ID = b.member_number
where a.board_ID is not NULL
[/code]

offspring22
Starting Member

38 Posts

Posted - 2009-11-10 : 13:18:33

Hmmm, that seems to make sense but I get the same results - just runs forever.

offspring22
Starting Member

38 Posts

Posted - 2009-11-10 : 13:41:49

note, public ID is null on some of the records on Userlist - which is mostly what I'm trying to correct.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 14:05:56

There must be a mistake on joining i.e. in the data in both tables.
Have a look at your data and have a special look at the possibility to join a.board_ID = b.member_number.
Maybe the join matches 60000 with 6000 because of missing proper ids?

No, you're never too old to Yak'n'Roll if you're too young to die.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-11-10 : 16:15:01

Are you familiar with Indexes? Do you know how to check if they exist on a table.

That could be the issue. Are there any indexes on the tables?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 16:35:00

Denis that cannot be with only 6000 records.

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-10 : 18:42:42

something must be blocking your query.

after executing it, open another window, type in "SP_WHO2" and look in the blocking column

cvraghu
Posting Yak Master

187 Posts

Posted - 2009-11-11 : 00:45:57

Try using hint - WITH (TABLOCKX) on the updated table

irinag9
Starting Member

1 Post

Posted - 2009-11-11 : 03:06:45

Hi! Try as I would write:

update userlist a
set a.public_id = (select b.public_ID
from member.dbo.members b
where a.board_ID = b.member_number)
where a.board_ID is not NULL

Hope board_ID is a unique index or primary key.

:)

cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources