Duplicating rows in SQL output too many values

Question

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

Possible duplicate of [Finding duplicate rows in SQL Server](https://stackoverflow.com/questions/2112618/finding-duplicate-rows-in-sql-server) — DarkSuniuM, Oct 21 '18 at 08:40

Barbaros Özhan · Accepted Answer · 2018-10-21T12:11:54.727

5

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.

edited Oct 21 '18 at 12:11

answered Oct 21 '18 at 08:24

Barbaros Özhan

47,993
9
26
51

1

@SarahAH If it worked and fixed your problem, you may wanna upvote the answer and accept it – DarkSuniuM Oct 21 '18 at 14:10

score 1 · Answer 2 · answered Oct 21 '18 at 09:37

1

I always use something like this:

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

answered Oct 21 '18 at 09:37

William Robertson

14,525
3
37
41

score 0 · Answer 3 · answered Oct 21 '18 at 12:03

0

This logic is also commonly written as:

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

answered Oct 21 '18 at 12:03

Gordon Linoff

1,198,228
53
572
709

Duplicating rows in SQL output too many values

3 Answers3