cvgopal123

 Profile Answers by cvgopal123 Questions by cvgopal123

• Feb 2nd, 2007

 

suppose if it is emp table which is having duplicate records...delete from emp where rowid not in(select max(rowid) from emp group by empno)

Jayanth Saimani

• Feb 7th, 2007

 

Every tuple in a database has a unique rowid.
For example if you created a table called student
When you type the query
select * from student
all the tuples have the attributes of the student are displayed
But what is not displayed is the rowid of every tuple
So for the duplicate tuple check what is the rowid..
and then give the query
delete from student where rowid='whatever rowid';
This will delete the duplicate row...
however this has to repeated for every duplicate tuple if any...

Shivendra

• Mar 1st, 2007

 

I think query should be (assuming STUDENT table has column NAME)

DELETE FROM STUDENT WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM STUDENT GROUP BY NAME )

naresh

• Mar 2nd, 2007

 

Delete * from emp where rowid not in (Select MAX(rowid) from emp group by deptno)

sibelius

 Profile Answers by sibelius 

• Mar 15th, 2007

 

Assuming there are several duplicates (in the general case):

delete from emp where rowid NOT IN (select min(rowid) from emp group by empno)

maiden123

 Profile Answers by maiden123 

• Mar 18th, 2007

 

first of all.. a relational database... that boast to exhibit ACID.. should never ever..allow duplicated rows
SQL..by default enforces uniquesness on a tuple..and voilation of Entity integrity is not alllowed in any database.. be it..SQL server, Oracle,Sybase,foxpro.. or even tera data..

However.. assume.. that our database designer is foollish enough to not enforce any sort of check in the database..
it actually happens in companies.. there are project that involves 10000 tables.. n 2.5 million tuples..(yeah..not jokin!!).. n it some times.. goes out of scope of designer.. or rather.. to say.. out of human ability to enforce promary, or foriegn key..
so there is none in the whole database.. as it makes life only more complex..
However....integrity is handles.. by explicitly writin triggers n checks..
suppose.. our designer has not even done this.. then what..
we are in big shit..
in above case.. if certian steps.. are to be follwed.. dat are laid down as defualt steps.. n only rescue.. in case of tuple duplicacy..

1. first find out..which bloody sets of tuples  causin the problem.

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2

2. second.. copy duplicate tuples.. in "OtherTable"...

SELECT col1, col2, col3=count(*)
INTO OtherTable
FROM t1
GROUP BY col1, col2


HAVING count(*) > 1
HAVING count(*) > 1

3. now select these rows.. in "onemoretable"..eleminatin it from "OtherTable"

SELECT DISTINCT t1.*
INTO OneMoreTable
FROM t1, OtherTable t
WHERE t1.col1 = t.col1
AND t1.col2 = t.col2

4. by this time.. onemoretable..should be havin only unique tuples..check it out

SELECT col1, col2, count(*)
FROM OneMoreTable

5. now safely delete it from "othertable".. and originalTable t1

DELETE t1
FROM t1, othertable t
WHERE t1.col1 = t.col1
AND t1.col2 = holdkey.col2
GROUP BY col1, col2

6. now put the unique n distinct tuples... in onemoretable back to t1

INSERT t1 SELECT * FROM holddups

7. enjoy.. and next time.. pleaz.. enforce entity integrity...!!!!!!!!!!

Sincerely,
ADIL. A. BAIG
(jobless....hahaha)

Lavanya Chowdary

• May 6th, 2007

 

We can use RowID if you are not having any primary key for column.

raji_4u

 Profile Answers by raji_4u 

• Sep 19th, 2008

 

Consider a table has two columns and no primary key

emp
---------------------
empno ename
---------------------
1 AA
2 BB
3 CC
1 DD --> not duplicate
3 CC --> duplicate
------------------------

DELETE FROM emp e1
WHERE rowid  >
(SELECT min (rowid) FROM emp e2

WHERE e1.empno = e2.empno
AND e1.ename = e2.ename)

The point here is, if the table has no primary key, we have to code the condition for all the columns in the table.
(here
"WHERE e1.empno = e2.empno AND e1.ename = e2.ename")