Answer / tharanath.n

delete from table a
where rowid >(select min(rowid) from table b
where a.column = b.column);

Answer / ajay kumar ande

question is wrong......records-----files,table-----rows
How will you delete duplicate records(rows) from a table?

pgm=sort
sortin dd dsn=-----
sortout dd dsn=-----
sysin dd *
sort fields=(2,3,ch,a)
sum fields=none ------>eliminates duplicate recs

Answer / venkat

delete from emp where ecode IN ( select ecode from emp group by ecode having count(*) >1 )

Answer / mehdee

Hi. This is a database design problem, because if you
alowe duplicate rows then you must have at least a column
being like squence number.

Answer / naresh.s

in DB2
first we need to know duplicate records in database file
like tmpfilep ,for that

select RRN(A) from tmpfilep A where field='fieldvalue'

here field means any fileld containd tmpfilep and
corresponding value.

once we execute the above query we will get relative record
numbers of each row and remember that numbers
now you can delete records based on these numbers

delete from tmpfilep A where rrn(A) in (234,341,642)

here 234,341,642 are sample record numbers

may be i am correct upto my knowledge.......

Answer / king

DELETE FROM FILE WHERE FIELD = ALL(SELECT FIELD FROM FILE
GROUP BY FIELD HAVING COUNT(*) > 1)


OR


DELETE FROM FILE WHERE FIELD = ANY(SELECT FIELD FROM FILE
GROUP BY FIELD HAVING COUNT(*) > 1)

Answer / s

DELETE FROM TABLE ABC
WHERE COLUMN =
(SELECT COLUMN FROM TABLE ABC
GROUP BY COLUMN
HAVING COUNT(*) > 1);

What do the initials DDL and DML stand for and what is their meaning?

2 Answers  

When would you prefer to use VARCHAR?

3 Answers  

Usually, which is more important for DB2 system performance - CPU processing or I/O access?

1 Answers  

What is the difference between nvl and coalesce?

0 Answers  

Can All Users Have The Privilege To Use The SQL Statement Select (DML)?

1 Answers  

How would you move a tablespace (using STOGROUP) to a different DASD volume allocated to that tablespace?

1 Answers  

I have a subprogram B calling from mainprogram A.I have opened a cursor in A ,Can i fetch the same cursor in subprogram B , If yes , Please explain the reason?

1 Answers   HeadStrong,

What is the COBOL picture clause for a DB2 column defined as DECIMAL(11,2)?

5 Answers   Emsang,

1. what if null values retrived from database and no null indicator mentioned in query. What is sql code.

1 Answers   Cap Gemini,

How can you split a table in to exactly half?

7 Answers   HSBC,

Explain the EXPLAIN statement?

1 Answers  

Suppose there are many duplicate records in a Db2 table. What is the query to retrive only duplicate records?

4 Answers