write a query to delete similar records in particular
fields(columns) in different tables
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
Answer / rajesh
DELETE emp WHERE ROWID NOT IN(SELECT MIN(eid)FROM emp GROUP
BY eid);
Is This Answer Correct ? | 1 Yes | 5 No |
what are the advantages of mysql in comparison to oracle? : Sql dba
What is RAC in oracle?
What is the purpose of primary key?
What is a sql select statement?
how to create a new table by selecting rows from another table in mysql? : Sql dba
What jobs use sql?
What found sql?
what is difference between delete and truncate commands? : Sql dba
How do I turn a list into a table?
difference between table level constraint and column level and advantages of table level constraint
Which one is faster ienumerable or iqueryable?
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...