Breaking News: Grepper is joining You.com. Read the official announcement!

sql delete duplicate rows but keep one

Add Answer

Upset Unicorn answered on March 13, 2020 Popularity 10/10 Helpfulness 3/10

answer sql delete duplicate rows but keep one

related sql remove duplicates

related Query to remove duplicate rows from a table

related sql delete duplicate

related sql delete duplicate rows

related SQL remove duplicate

related DELETE DUPLICATE VALUES FROM A TABLE IN SQL SERVER

related how to delete one of the duplicates sql

related delete duplicate records from table rownum

related sql remove duplicates based on column

sql delete duplicate rows but keep one

Comment

# Step 1: Copy distinct values to temporary table
CREATE TEMPORARY TABLE tmp_user (
    SELECT id, name 
    FROM user
    GROUP BY name
);

# Step 2: Remove all rows from original table
DELETE FROM user;

# Step 3: Remove all rows from original table
INSERT INTO user (SELECT * FROM tmp_user);

# Step 4: Remove temporary table
DROP TABLE tmp_user;

Popularity 10/10 Helpfulness 3/10 Language sql

Source: www.designcise.com

Tags: rows sql sql-delete

Link to this answer
Share Copy Link

Contributed on Mar 13 2020

Upset Unicorn

0 Answers Avg Quality 2/10

Closely Related Answers

sql remove duplicates

Comment

WITH cte AS (
    SELECT 
        contact_id, 
        first_name, 
        last_name, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name, 
                last_name, 
                email
            ORDER BY 
                first_name, 
                last_name, 
                email
        ) row_num
     FROM 
        sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;
/*
Code language: SQL (Structured Query Language) (sql)

In this statement:

First, the CTE uses the ROW_NUMBER() function to find the duplicate rows 
specified by values in the first_name, last_name, and email columns.

Then, the DELETE statement deletes all the duplicate rows but keeps only one 
occurrence of each duplicate group.*/

Popularity 10/10 Helpfulness 4/10 Language sql

Source: www.sqlservertutorial.net

Tags: sql

Link to this answer
Share Copy Link

Contributed on May 24 2022

Lucas Gomes

0 Answers Avg Quality 2/10

Query to remove duplicate rows from a table

Comment

DELETE FROM Customers WHERE ROWID(SELECT MAX (rowid) FROM Customers C WHERE CustomerNumber = C.CustomerNumber);

Popularity 10/10 Helpfulness 4/10 Language sql

Source: www.edureka.co

Tags: rows

Link to this answer
Share Copy Link

Contributed on Nov 19 2021

Beautiful Baboon

0 Answers Avg Quality 2/10

sql delete duplicate

Comment

-- Oracle
DELETE FROM films
WHERE rowid NOT IN (
    SELECT min(rowid)
    FROM films
    GROUP BY title, uk_release_date
);

Popularity 10/10 Helpfulness 4/10 Language sql

Source: Grepper

Tags: sql

Link to this answer
Share Copy Link

Contributed on Apr 13 2021

VasteMonde

0 Answers Avg Quality 2/10

sql delete duplicate rows

Comments(1)

WITH cte AS (
    SELECT 
        contact_id, 
        first_name, 
        last_name, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name, 
                last_name, 
                email
            ORDER BY 
                first_name, 
                last_name, 
                email
        ) row_num
     FROM 
        sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;

Popularity 10/10 Helpfulness 4/10 Language sql

Source: www.sqlservertutorial.net

Tags: rows

Link to this answer
Share Copy Link

Contributed on Jun 17 2022

Dead Dotterel

0 Answers Avg Quality 2/10

SQL remove duplicate

Comment

DELETE FROM [SampleDB].[dbo].[Employee]
    WHERE ID NOT IN
    (
        SELECT MAX(ID) AS MaxRecordID
        FROM [SampleDB].[dbo].[Employee]
        GROUP BY [FirstName], 
                 [LastName], 
                 [Country]
    );

Popularity 10/10 Helpfulness 4/10 Language sql

Source: www.sqlshack.com

Tags: sql

Link to this answer
Share Copy Link

Contributed on Apr 11 2022

WilsonWW

0 Answers Avg Quality 2/10

DELETE DUPLICATE VALUES FROM A TABLE IN SQL SERVER

Comment

WITH cte AS (
    SELECT 
        contact_id, 
        first_name, 
        last_name, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name, 
                last_name, 
                email
            ORDER BY 
                first_name, 
                last_name, 
                email
        ) row_num
     FROM 
        sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;
Code language: SQL (Structured Query Language) (sql)

Popularity 10/10 Helpfulness 3/10 Language sql

Source: www.sqlservertutorial.net

Tags: sql

Link to this answer
Share Copy Link

Contributed on Jan 20 2022

Vast Vole

0 Answers Avg Quality 2/10

how to delete one of the duplicates sql

Comment

SET ROWCOUNT 1
DELETE [dbo].[table] where ....
SET ROWCOUNT 0

Popularity 9/10 Helpfulness 3/10 Language sql

Source: Grepper

Tags: sql

Link to this answer
Share Copy Link

Contributed on Apr 12 2023

Ruben Visser

0 Answers Avg Quality 2/10

delete duplicate records from table rownum

Comment

DELETE FROM Customers WHERE ROWID NOT IN (SELECT MAX (rowid) FROM Customers C WHERE CustomerNumber = C.CustomerNumber);

Popularity 7/10 Helpfulness 3/10 Language whatever

Source: Grepper

Tags: records rownum whatever whateve

Link to this answer
Share Copy Link

Contributed on Mar 31 2023

Nervous Nightingale

0 Answers Avg Quality 2/10

sql remove duplicates based on column

Comment

SELECT s.siteName, s.siteIP, h.date
FROM sites s INNER JOIN
     (select h.*, row_number() over (partition by siteName order by date desc) as seqnum
      from history h
     ) h
    ON s.siteName = h.siteName and seqnum = 1
ORDER BY s.siteName, h.date

Popularity 10/10 Helpfulness 3/10 Language sql

Source: stackoverflow.com

Tags: sql

Link to this answer
Share Copy Link

Contributed on Nov 29 2021

Mysterious Moose

0 Answers Avg Quality 2/10

sql delete duplicate rows but keep one

Contents

More Related Answers

sql delete duplicate rows but keep one

Closely Related Answers

sql remove duplicates

Query to remove duplicate rows from a table

sql delete duplicate

sql delete duplicate rows

SQL remove duplicate

DELETE DUPLICATE VALUES FROM A TABLE IN SQL SERVER

how to delete one of the duplicates sql

delete duplicate records from table rownum

sql remove duplicates based on column

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.