write a query to delete similar records in particular fields(columns) in differ

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

write a query to delete similar records in particular
fields(columns) in different tables

Question Posted / pk

6 Answers
11807 Views
TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

write a query to delete similar records in particular fields(columns) in different tables..

Answer / ram

Guys, Please see the question again. It is related to
deleting different tables. I guess, the question is
intented to get the answer - ON DELETE CASCADE. We give
foreign constraints on tables and when the parent record is
deleted, the child records are automatically deleted when
you give command as ON DELETE CASCADE

Is This Answer Correct ?

4 Yes

1 No

write a query to delete similar records in particular fields(columns) in different tables..

Answer / madhu

delete from emp where emp_no in
(select emp_no from emp2 where emp.emp_no=emp2.emp_no)

Is This Answer Correct ?

7 Yes

4 No

write a query to delete similar records in particular fields(columns) in different tables..

Answer / ramkumar v

CREATE OR REPLACE PROCEDURE DUP AS

DECLARE
TABLENAME_TMP TABLE;

CURSOR C1 IS
SELECT M.TABLE_NAME
FROM USER_TAB_COLS M
WHERE M.COLUMN_NAME LIKE 'EMPNO';

BEGIN

OPEN C1;
LOOP
FETCH C1 INTO TABLENAME_TMP;
WHEN C1%NOTFOUND THEN EXIT;

DELETE FROM TABLENAME_TMP A WHERE ROWID NOT IN
(SELECT MAX(ROWID) FROM TABLENAME_TMP B

WHERE A.EMPNO>=B.EMPNO);
ENDLOOP;
CLOSE C1;
END DUP;

Is This Answer Correct ?

4 Yes

4 No

write a query to delete similar records in particular fields(columns) in different tables..

Answer / khandu shinde

delete from emp where rowid not in ( select max(rowid) from
emp group by empno)

Is This Answer Correct ?

7 Yes

9 No

write a query to delete similar records in particular fields(columns) in different tables..

Answer / satyam kumar

Hi,

I have manipulated Khandu Shinde answer because it will
delete complete row.

delete from emp where rowid not in ( select max(rowid) from
emp group by empno having count(redundantcolumnName) > 1)

Note: work only with Oracle.

Is This Answer Correct ?

1 Yes

5 No

write a query to delete similar records in particular fields(columns) in different tables..

Answer / rajesh

DELETE emp WHERE ROWID NOT IN(SELECT MIN(eid)FROM emp GROUP
BY eid);

Is This Answer Correct ?

1 Yes

5 No

Post New Answer