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 is -904 sql code? How to resolve it?

3 Answers  

What is database reorganization?

0 Answers  

Is it Possible to read from PS file and write it to database by using dynamic sql(execute immediate)

2 Answers  

how to copy the host variables from ps file into cobol program other than include statement

1 Answers   Syntel,

Shall i use this query to retrieve first 4 records, Select empno, sal from emptbl where empno < 5. like this can we fetch first 100 records?

3 Answers  

Explain various types of locks in db2?

0 Answers  

Say CUST Table contains records like: CUSTNO CUSTNAME CUSTLOC 100 ABC SSS 200 XYZ 300 PQR 400 MNO WWW 500 CVV ------------- ------------- Now write a query to retrieve all records with CUSTLOC no data.

3 Answers   IBM,

What does CURRENTDATA option in bind indicate

1 Answers   IBM,

How do you retrieve the first 5 characters of firstname column of db2 table emp?

1 Answers  

what is the differences between spufi and qmf and which is better?

4 Answers   Accenture, Cap Gemini,

What is the syntax of SELECT statement when embedded in a COBOL program?

2 Answers  

What does the REORG Utility do?

2 Answers