Geek asks and answers

Delete duplicates without primary key

Here you need to delete rows with the duplicated column value ( Product), which will then be used as the primary key .

The column is of type nvarchar, and we do not want to have 2 rows for one product. The database is large with the size of thousands of rows to be deleted.

During the query for all duplicates, we want to keep the first element and delete the second as a duplicate.

There is no primary key yet, and we want to do this after this duplicate delete operation. Then maybe our main key.

Database is SQL Server CE.

I tried several methods and basically got an error similar to:

An error occurred while parsing the request. [Token line number = 2, token line offset = 1, error token = FROM]

The method I tried:

DELETE FROM TblProducts
FROM TblProducts w
    INNER JOIN (
            SELECT Product
            FROM TblProducts
            GROUP BY Product
            HAVING COUNT(*) > 1
            )Dup ON w.Product = Dup.Product

The preferred way to try to find out and configure my code with something similar (This has not been fixed yet):

SELECT Product, COUNT(*) TotalCount
FROM TblProducts
GROUP BY Product
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

--
;WITH cte   -- These 3 lines are the lines I have more doubt on them
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Product
                                       ORDER BY ( SELECT 0)) RN
         FROM   Word)
DELETE FROM cte
WHERE  RN > 1
+5
source share
4 answers

If you have two DIFFERENT records with the same Product column, you can select unwanted records with some criteria, for example.

 CREATE TABLE victims AS
     SELECT MAX(entryDate) AS date, Product, COUNT(*) AS dups FROM ProductsTable WHERE ...
     GROUP BY Product HAVING dups > 1;

Then you can do DELETE JOIN between ProductTable and victims.

, DELETE - JOIN, , CustomerId NULL EntryDate - . , , , .

, ( , - , , ). . SELECT ProductsTable SELECT DISTINCT , , , ( , . MAX MIN). "" .

DELETE JOIN . reimport .

, DELETE JOIN INSERT SELECT , .

, MySQL:

-- Create an empty table
CREATE TABLE deduped AS SELECT * FROM ProductsTable WHERE false;

CREATE UNIQUE INDEX deduped_ndx ON deduped(Product);

-- DROP duplicate rows, Joe the Butcher way
INSERT IGNORE INTO deduped SELECT * FROM ProductsTable;

ALTER TABLE ProductsTable RENAME TO ProductsBackup;

ALTER TABLE deduped RENAME TO ProductsTable;
-- TODO: Copy all indexes from ProductsTable on deduped.

: , " " " ". , DUPLICATE, , !

: , "" . :

  SELECT * FROM ProductsTable ORDER BY Product, FieldWhichShouldNotBeNULL IS NULL;

, , , . , , (FieldWhichShouldNeverBeNull IS NULL) FALSE ( , FieldWhichShouldNeverBeNull , ), . , - IGNORE, . ( , true false !), .


,

CREATE TABLE ProductTable ( Product varchar(10), Description varchar(10) );
INSERT INTO ProductTable VALUES ( 'CBPD10', 'C-Beam Prj' );
INSERT INTO ProductTable VALUES ( 'CBPD11', 'C Proj Mk2' );
INSERT INTO ProductTable VALUES ( 'CBPD12', 'C Proj Mk3' );

. .

- . , NULL.

, , CBPD14 , . CBPD10.

INSERT INTO ProductTable VALUES ( 'CBPD10', NULL );
INSERT INTO ProductTable VALUES ( 'CBPD14', NULL );

ProductTable WHERE IS NULL , CBPD14, .

, . :

SELECT Product, COUNT(*) AS Dups FROM ProductTable GROUP BY Product HAVING Dups > 1;

, : " ".

, . , , .

SELECT Dups.Product FROM ProductTable
RIGHT JOIN ( SELECT Product, COUNT(*) AS Dups FROM ProductTable GROUP BY Product HAVING Dups > 1 ) AS Dups
ON (ProductTable.Product = Dups.Product
        AND ProductTable.Description IS NOT NULL)
WHERE ProductTable.Description IS NULL;

, , ; , .

INSERT INTO ProductTable VALUES ( "AC5", NULL ), ( "AC5", NULL );

"check" ,

AC5

, Dups .

, . , , , - , "" "" (, , ).

, . (http://dev.mysql.com/doc/refman/5.0/en/delete.html). :

CREATE TEMPORARY TABLE Dups AS
     SELECT Product, COUNT(*) AS Duplicates
         FROM ProductTable GROUP BY Product HAVING Duplicates > 1;

DELETE ProductTable FROM ProductTable JOIN Dups USING (Product)
    WHERE Description IS NULL;

, , Dups.

CBPD14 , . "" CBPD10 , , NULL. - .

, , , - .

, , SELECT ( , ", " ) , , , ( ).

+4

, . script ( ..) . . , . , .

, . , , . , .

+1

, , Sql Server Management, . , CTE, , . ,

SELECT MIN(table_a.MyTempIDField)
FROM
table_a lhs
join table_1 rhs
 on lhs.field1 = rhs.field1
 and lhs.field2 = rhs.field2 [etc]
WHERE
 table_a.MyTempIDField <> table_b.MyTempIDField
GROUP BY
 lhs.field1, rhs.field2 etc

"" . DELETE FROM.

DELETE FROM lhs
FROM table_a lhs
join table_b rhs
 on lhs.field1 = rhs.field1
 and lhs.field2 = rhs.field2 [etc]
WHERE
 lhs.MyTempIDField <> rhs.MyTempIDField
 and lhs.MyTempIDField not in (

SELECT MIN(lhs.MyTempIDField)
FROM
table_a lhs
join table_a rhs
 on lhs.field1 = rhs.field1
 and lhs.field2 = rhs.field2 [etc]
WHERE
 lhs.MyTempIDField <> rhs.MyTempIDField
GROUP BY
  lhs.field1, lhs.field2 etc
)
+1

:

DELETE FROM TblProducts     
WHERE Product IN
      (
     SELECT Product
     FROM TblProducts
     GROUP BY Product
     HAVING COUNT(*) > 1)

This is due to a defect that it deletes ALL records with a duplicate product. What you probably want to do is delete all but one group of records with this Product. Perhaps you should first copy all the duplicates into a separate table, and then somehow remove the duplicates from this table, and then apply the above, and then copy the remaining products back to the original table.

-2
source

More articles:


All Articles