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

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

More SQL PLSQL Interview Questions

what are the advantages of mysql in comparison to oracle? : Sql dba

0 Answers  


What is RAC in oracle?

7 Answers   MCN Solutions,


What is the purpose of primary key?

0 Answers  


What is a sql select statement?

0 Answers  


how to create a new table by selecting rows from another table in mysql? : Sql dba

0 Answers  






What jobs use sql?

0 Answers  


What found sql?

0 Answers  


what is difference between delete and truncate commands? : Sql dba

0 Answers  


How do I turn a list into a table?

0 Answers  


difference between table level constraint and column level and advantages of table level constraint

4 Answers   Doyensys,


Which one is faster ienumerable or iqueryable?

0 Answers  


1. is it possible to use the cursor atttibutes (%found ,% rowcount , %isopen , %notfound ) to our user defined cursor names ....... cursor cursor_name is select * from scott.emp if you use... cursor_name%found , %rowcount ,%isopen,%notfound...will it work... -------------------------- 2.what is the difference between the varray and index by table .. -------- 3. type type_name is table of number(8,3) index by binary_integer; identifier_name type_name; first , last , prior , next ,trim are the methods we can use it for the above type...simillary is there any way to apply for cursors... with thanks and regards..sarao...

0 Answers   Satyam,


Categories