[SOLVED] Delete duplicate rows using SQL and window function

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

Solution

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;