Question 246935 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Delete duplicate rows using SQL and window function

In the following table

create table employee (emp_id integer, emp_name varchar(50), dept_id integer);
insert into employee values (1,'bob william',1);
insert into employee values (2,'scott tiger',1);
insert into employee values (3,'amit gupta',1);
insert into employee values (4,'vijay sinha',1);
insert into employee values (5,'miller jones',3);
insert into employee values (6,'adam clark',3);
insert into employee values (7,'smith stuart',3);
insert into employee values (8,'james ward',4);
insert into employee values (9,'adam black',4);
insert into employee values (7,'smith stuart',3);
insert into employee values (8,'james ward',4);
insert into employee values (9,'adam black',4);

I am looking for duplicates rows based on emp_name column.
I tried the following code:

WITH CTE_Table AS 
(SELECT *, 
ROW_NUMBER() OVER(PARTITION BY emp_name ORDER BY emp_name) AS RN
FROM employee
)
DELETE FROM CTE_Table
WHERE RN > 1

I get the following message: ‘ERROR: relation "cte_table" does not exist
LINE 6: DELETE FROM CTE_Table’

Please help. I am using PostgreSQL with PG Admin 4

Tags:

2

Answers


  1. 0

    Deleting from a CTE is not possible

    But i recommend you instead of it to work with temp tables;

    Your CTE as TEMPORARY TABLE will look so;

    CREATE TEMPORARY TABLE temp_table AS
SELECT *,
       ROW_NUMBER() OVER(PARTITION BY emp_name ORDER BY emp_name) AS RN
FROM employee;
DELETE FROM temp_table where RN > 1;
    Login or Signup to reply.
  2. 0

    You can use CTE to identify the duplicates and then DELETE based on the result of the CTE;

    WITH CTE_Duplicates AS (
    SELECT emp_id, emp_name, dept_id
    FROM employee
    WHERE emp_id NOT IN (
        SELECT MIN(emp_id)
        FROM employee
        GROUP BY emp_name
        HAVING COUNT(*) > 1
    )
)
DELETE FROM employee
WHERE emp_id IN (SELECT emp_id FROM CTE_Duplicates);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search