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 |
|
mshsilver
Posting Yak Master
112 Posts |
 Posted - 2011-12-06 : 06:45:49
|
Hi,I have a query that I am using to show me orphan records but for some reason the result when I run the query is coming back with no rows. Once i see the reult i want to delete them.Select * from history where not uniqueid in (select luniqueid from linkto where lutablename = 'history' and letablename = 'contact') I know that this result is incorrect because i can find thousands of uniqueid's that do not exist in the wce_linkto table that exist in the wce_history table if I search manually one by one.I was wondering if it the size of my history table and the size of the linkto. There area about 2.5 million rows in each table.I have also tried this in a small scale environment where there are just a few hundred records and it works fine.Is there anything I can do to resolve this? Tests I can run or any other queries I can try? Thanks for looking. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-06 : 06:51:06
|
trySelect * from history h where not exists(select 1 from linkto where lutablename = 'history' and letablename = 'contact' and luniqueid =h.uniqueid ) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
mshsilver
Posting Yak Master
112 Posts |
Posted - 2011-12-06 : 07:01:47
|
| Thanks for the quick reply. That is getting results! Would i be safe to run my delete from that query? It is a production database so i need to be very careful. I have checked a few rows but can't check them all because there are over 70,000 rows. The next issue is the scale of the rows to delete, is there a way in that query that I can get it to delete 5000 at a time? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-06 : 07:09:26
|
| my suggestion is since its production database, you need to check all records and be 100 % sure before you go with deletionfor batch deletion you can put a looping logic based on primary key value. start with minimum value and iterate till maximum value keeping on deleting 5000 records for each iteration.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
mshsilver
Posting Yak Master
112 Posts |
Posted - 2011-12-06 : 07:15:35
|
| Thank you for that, very helpful. I will go and do some tests now :-) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-06 : 07:31:03
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-12-06 : 19:56:01
|
| I suspect that your original WHERE clause was letting you down because the SELECT in the IN operator was returning a NULL value.=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-06 : 23:24:44
|
quote:
Originally posted by Bustaz Kool
I suspect that your original WHERE clause was letting you down because the SELECT in the IN operator was returning a NULL value.=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith
That was my guess too. thats why i used exists instead------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
SQL mass delete