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

DarkSuniuM
– DarkSuniuM

2018-10-21 08:40:33 +00:00
Commented Oct 21, 2018 at 8:40 — DarkSuniuM
– DarkSuniuM, Commented Oct 21, 2018 at 8:40

Barbaros Özhan · Accepted Answer · 2018-10-21 12:11:54Z

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, 2018 at 12:11

answered Oct 21, 2018 at 8:24

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DarkSuniuM Over a year ago

@SarahAH If it worked and fixed your problem, you may wanna upvote the answer and accept it

William Robertson · Accepted Answer · 2018-10-21 09:37:34Z

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, 2018 at 9:37

William Robertson

16.2k4 gold badges40 silver badges49 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-10-21 12:03:55Z

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, 2018 at 12:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Duplicating rows in SQL output too many values

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related