deleting only X number of records... 
Author Message
 deleting only X number of records...

I have a reference table that is not keyed and I need to delete some of the
records.

The table has 37K distinct records and over 2 million total records.  The
table data is duplicative in design.

The table tracks occurances of 4 vairables each having its own field in the
table.  the log file that feeds the table was processed multiple times
causing some erroneous entries in the table.

In a nutshell, if I have 212 records that represent the variable sequence
A:B:C:D and I know that there are 64 erroneous entries, how do I delete the
64 records but keep the remaining 148?

thanks in advance...



Sat, 04 Jun 2005 12:36:40 GMT
 deleting only X number of records...

Bill,

At least you know you've got problems !

In general you cannot delete only one row from a table that is duplicated in
all respects by another.  There just isn't anything you can put into
a where clause to separate the rows.  You could SELECT DISTINCT into
another table, which has an IDENTITY and use that as the hook for
MAX/MIN type selects in DELETE and GROUP BY type logic for
sanitising/duplicate deletion.

From your narrative though I'm also not entirely clear on the problem.
Do you want to end up with 37K rows or 148?  Sequence? what sequence?
 What do you mean by erroneous ?  - Duplicated, or damaged some other way?

Table structures would make this clearer.

select distinct A, B, C, D
into NewTable
from BadTable

May be all you are after - it's a bit hard to tell

Regards
 AJ

Quote:

> I have a reference table that is not keyed and I need to delete some of the
> records.

> The table has 37K distinct records and over 2 million total records.  The
> table data is duplicative in design.

> The table tracks occurances of 4 vairables each having its own field in the
> table.  the log file that feeds the table was processed multiple times
> causing some erroneous entries in the table.

> In a nutshell, if I have 212 records that represent the variable sequence
> A:B:C:D and I know that there are 64 erroneous entries, how do I delete the
> 64 records but keep the remaining 148?

> thanks in advance...



Sat, 04 Jun 2005 14:15:21 GMT
 deleting only X number of records...
Bill
If I undestood you correctly

CREATE TABLE #W
(
COL INT IDENTITY PRIMARY KEY,
COL1 CHAR(1) NOT NULL,
COL2 VARCHAR(10) NOT NULL
)

INSERT INTO #W VALUES ('A','ALEX')
INSERT INTO #W VALUES ('D','DAN')
INSERT INTO #W VALUES ('G','GREG')
INSERT INTO #W VALUES ('B','BILL')
INSERT INTO #W VALUES ('F','FRED')
INSERT INTO #W VALUES ('L','LIOR')
INSERT INTO #W VALUES ('M','MIKE')
INSERT INTO #W VALUES ('E','ERAN')
INSERT INTO #W VALUES ('Q','QQ')
INSERT INTO #W VALUES ('U','URI')
INSERT INTO #W VALUES ('C','CORN')

SELECT * FROM #W ORDER BY COL1
-----LET'S SAY YOU WANT TO DELETE FROM A TO D
DELETE FROM #W WHERE COL1 IN
   (SELECT COL1 FROM #W WHERE COL1 BETWEEN 'A' AND 'D')

----LET'S SAY YOU WANT TO DELETE FROM 5 TO 8
SELECT COL FROM #W
WHERE(SELECT COUNT(COL) FROM #W T WHERE T.COL <=#W.COL)
BETWEEN 5 AND 8

Quote:

> I have a reference table that is not keyed and I need to delete some of the
> records.

> The table has 37K distinct records and over 2 million total records.  The
> table data is duplicative in design.

> The table tracks occurances of 4 vairables each having its own field in the
> table.  the log file that feeds the table was processed multiple times
> causing some erroneous entries in the table.

> In a nutshell, if I have 212 records that represent the variable sequence
> A:B:C:D and I know that there are 64 erroneous entries, how do I delete the
> 64 records but keep the remaining 148?

> thanks in advance...



Sat, 04 Jun 2005 15:23:48 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Find certain Number of Records / Print only certain number of records

2. Error 3167 Record Deleted when there is no deleted record

3. SQL Question: Deleting certain number of records???

4. Deleting large number of records

5. how to delete record by row number

6. Optimizing delete of large number of records

7. Deleting Records and Index Number

8. Deleting Large number of records from an Oracle table

9. Help:Record/Key Deleted message and Error message Numbers

10. Record is deleted, Error number 3167, DAO.Tabledefs.

11. deleting large number of records from table...

12. Best way to record number of selects,update,delete's during interval


 
Powered by phpBB® Forum Software