SQL & PL/SQL :: Delete Duplicate Record From Table?
May 12, 2011
I have written this below code. The logic behind the code is, Delete the duplicate record from a table, and delete those record from other 7 table based on the SL_NUMBER.
But Problem is After delete the duplicate record When I have use Below statement
RETURNING SL_NUMBER BULK COLLECT INTO rec_sl_number;
This statement unable to return approx 40 Lakhs SL_NUMBER
DECLARE
rec_sl_number dbms_sql.number_table;
BEGIN
I have a requirement to delete duplicate records. For example,if the below query retrieves count of duplicate records along with the column values.
select col2,col3,col4,col5,col6,col7,count(*) from table group by col2,col3,col4,col5,col6,col7 having count(*) > 1;
I would like to retain only one record with max(col1) which is a surrogate key and other records should be deleted.How to retain one record in a duplicate record set based on max of certain column.
Created three tables and group by 3 tables column name. want to delete duplicate record without first table(test). Delete the duplicate record in test1 and test2 except test.
SELECT a as Name,b as M_Name, c as L_Name, count(*) FROM ( SELECT first_name as a, middle_name as b, last_name as c FROM test UNION ALL SELECT first_name as a, middle_name as b, last_name as c FROM test1 UNION ALL SELECT first_name as a, middle_name as b, last_name as c FROM test2 ) as countGROUP BY a,b,cHAVING count(*) > 1
In oracle 9i ........How to delete duplicate entries in a table ?
if suppose in a table named office, one or more entry(row) is repeated more then twice or minimum twice. I need a query to delete the multiple entries ....
Note: --->No constraints applied on the table. --->No Primary Key --->You cannot create any object....that is no view or a duplicate table can be created
My aim is to delete duplicates out of above data, with the below condtions.
1) first record with value 30 and then with value 0. 2) if there are 3 duplicate records ex: ID is 1 and Group KK, then i have to delete both 30 & 0 qty records. 3) If there are more than 3 duplicate records ex: ID is 3 and Group is AA, the i have to delete all the records with qty value either 30 or 0 and.
I have written a query like below.
SELECT id, unit, RANK () OVER (PARTITION BY id, unit ORDER BY id, unit) num FROM temp;
with the above query, i am unable to mark this dynamic duplications.
id name plan code 1 sam normal 5 1 sam normal 6 1 sam special 5 1 sam Special 6
I need to delete data in such a way that one entry with normal and one entry with special plan should remain and should be with different code. Does not matter whether normal stays with 5 or 6 code.
I tried with rowid but it deletes either both normal or both special or returns same code for normal and special.
I looking for a cursor where i can find duplicate rows in a table. Like i have emp table in which i have deptno column. I have four with same deptno e.g 10. Now my requirement is after i get the 1 record with deptno 10 , i need a message that shows remaining 3 record as duplicate entry except the very first entry.
Is it possible to apply primary key on table having some duplicate record?I can do this by deleting duplicate record, But I don't want to delete exisitng data.
how to select 1st record from duplicate vales in a table.
If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.
but here duplicate values r there.
SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;
how to delete duplicated records from a table without using row_id. I found the duplicated rows from a table using Analytical Function. But i could not use the Analytical function in the where condition.
I have following query: type EMP_REC is record ( id number, name varchar2(20), dept number );
type EMP_TBL IS TABLE OF EMP_REC INDEX BY BINARY_INTIGER; EMP_TABL1 EMP_TBL;
select * BULK COLLECT into EMP_TBL1 FROM emp;
How to remove duplicate records from EMP_TBL1 collection if exists.I don't want to remove duplicate records from main table. But actually want to remove duplicate records from EMP_TBL1 collection if exists.
I have one form in which there are master detail blocks. I am entering one record in master block and corresponding entry in detail block. If again I am entering a new record in master block, of course the corresponding entry is getting erased since the block is getting changed.
After entering the data in master block I want to pop a message as 'whether you want to duplicate the same entry in detail block '. If yes, then how can I copy the same details which i have entered for previous record? Can I use duplicate_record built in? If yes, How?
here line_id is primary key .here i don't want to enter duplicate category code when i submit rows.Here Allocation_id and service_id would be same for particular service_id .How can i create validation to enter duplicate category code .
I want to delete the duplicated record using following methods, it delete the records if all the record are matched according to condition, but if all records are not matched then it display error messages.
SQL> SELECT * FROM XYZ;
NAME FNAME ADDRESS JOB -------------- -------------------- -------------------- ----------------------------------------- Bilal Khan Wali your rehman name district abbottabad student Bilal Khan Wali your rehman name district abbottaba student Bilal Kh Wali your rehma name district abbotta studen Bilal K Wali your rehm name district abbotta studen Bilal Khan Wali your rehman name istrict abbottaba tudent lal Khan i your rehman name strict abbottaba tudent [code]....
we have a table attendance_d with no constraint which have duplicate emp_id we want to stop duplicate emp_id on the same date. if employee's record already entered in today's date then duplicate Error message must show if he tries again to enter the same record. for this i have written the following code but it is not working date wise some body. i want to use on WHEN VALIDATE ITEM TRIGGER in oracle forms 6i.
DECLARE l_count NUMBER; BEGIN
[Code]....
i have tried my best to format the syntax of code but in preview it showing like as above i have formated in toad by using the key ctrl+shift+f.
the following case is successfully done with mssql databases.
Case:
Table UserGroup Columns id, name, handshake
When the handshake is set to 'd', this record should be deleted. I know it is bad behaviour by design.
What have I done so far:
- created an after update trigger (mutual error) Caused by trying a delete action in the update action, not possible.
- created a view in combination of instead of update trigger.
This causes also mutual error, or if ignored (PRAGMA AUTONOMOUS_TRANSACTION), an deadlock.
Code so far:
create or replace procedure Delete_UserGroup_sp(p_groupId in USER_GROUP.HMIUSERGROUPID%TYPE, p_handshake in USER_GROUP.HANDSHAKE%TYPE) is begin if p_handshake = 'd' then delete USER_GROUP WHERE HMIUSERGROUPID = p_groupId; commit; end if; end;
create or replace view USERGROUP_V as select * from USER_GROUP
create or replace trigger USER_GROUP_T1 instead of update on USERGROUP_V for each row declare PRAGMA AUTONOMOUS_TRANSACTION; begin Delete_UserGroup_sp(:new.HMIUSERGROUPID, :new.HANDSHAKE); end;
when i tried to delete the record by clicking the delete button which has the trigger code as
delete from emp where employee_id=:e_id; commit;
it wasn't. and showed the message as ---FRM-40508: ORACLE ERROR: Unable to INSERT record.
if i change the EMP ID item property "database item" to "NO"..then i can able to delete the record..but, now i am unable to insert the record form the same form...
