Duplicating rows in SQL output too many values

user_5613558 0
Viewed 172

I created a query to delete duplicate rows in a table. I tried to did this but the output was ”too many values”

DELETE FROM Employeetest 
WHERE employeeid IN (SELECT 
                         employeetest.*, 
                         ROW_NUMBER() OVER (PARTITION BY employeeid ORDER BY employeeid) AS rownumber 
                     FROM
                         employeetest 
                     HAVING
                         (rownumber > 1));

I did also

SELECT
    employeetest.*, 
    ROW_NUMBER() OVER (PARTITION BY employeeid ORDER BY employeeid) AS rownumber 
FROM
    employeetest

then 

DELETE * FROM employeetest;

It's not working

sql-delete oracle sql
3 Answers

you tagged as plsql that I understand your DB is Oracle. So you may use rowid pseudocolumn as in the following :

delete Employeetest t1
where rowid <
(
select max(rowid)
  from Employeetest t2 
 where t2.employeeid = t1.employeeid 
);

if the aim is deleting all the duplicates of employeeid values.

P.S. It's not possible to delete in such a way Delete * from employeetest where ..., but using Delete from employeetest where ... or Delete employeetest where ... are possible options.

user_58413060

I always use something like this:

delete employeetest
where  rowid in
       ( select lag(rowid) over (partition by employeeid order by null) 
         from employeetest )
user_2304710

This logic is also commonly written as:

delete Employeetest 
where rowid in (select max(rowid)
                from Employeetest e2
                group by e2.employeeid
               );
user_11440350
Related