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.
| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2010-04-27 : 08:46:39
|
This query is on a single table about 620,000 rows. Some of the records have blank or null description fields. So, what I am doing is scanning the table for a like RC and if it has something in the desc field, I use that to update the others that have that same RC. The query works, but takes an average of 22 minutes and there is nothing else going on on the server. Both fields are indexed. Here is the query:UPDATE ERS_ePeople SET RC_DESC4 = (SELECT TOP 1 RC_DESC4FROM ERS_ePeople E WHERE E.RC4 = ERS_ePeople.RC4 AND RC_DESC4 >'' ORDER BY RC_DESC4)WHERE RC_DESC4 IS NULL OR RC_DESC4 = '' I know there is probably a more efficient way to write this. Thank you.Duane |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-27 : 08:55:01
|
Try this:update p set RC_DESC4 = dt.RC_DESC4from ERS_ePeople as p join (select RC4, min(RC_DESC4) as RC_DESC4 from ERS_ePeople group by RC4)dt on dt.RC4 = p.RC4 and isnull(dt.RC_DESC4,'') <> ''where isnull(RC_DESC4,'') = '' edit: changed max() to min() (Thanks to Kristen)
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-27 : 08:57:50
|
SELECT TOP 1 RC_DESC4...ORDER BY RC_DESC4should perhaps be(select RC4, min(RC_DESC4) as RC_DESC4 from ERS_ePeople group by RC4) ?? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-27 : 09:10:26
|
quote:
Originally posted by Kristen
SELECT TOP 1 RC_DESC4...ORDER BY RC_DESC4should perhaps be(select RC4, min(RC_DESC4) as RC_DESC4 from ERS_ePeople group by RC4) ??
I'm embarrassed  Thanks for looking so precisely 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-27 : 09:50:21
|
Well, in fairness, if I had written it (which I would have done your way), I would have used MAX in order not to get a blank one (rather than excluding blanks in the WHERE / ON), so I reckon you were right ... in a way  |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-04-27 : 09:55:22
|
| Thank you for all the responses. I am going to try these on a test table and see which one works the fastest. I know I can tend to end up with some pretty quirky ways of doing things that end up working, but not very efficiently. I am still very much in a learning curve.Duane |
|
|
|
|
|
|
|
|
Update query taking too long - Why?