SQL & PL/SQL :: Deleting Duplicate Records Based On Repetition Of Certain Fields

Sep 15, 2011

I have the following situation and need support:

create table try_x
(a number PRIMARY KEY,
b NUMBER,
c NUMBER,
f_text VARCHAR2(10));

insert ALL
into try_x values (0,1,1,'abc')
into try_x values (1,1,1,'abc')
into try_x values (2,1,1,'xyz')
into try_x values (3,1,2,'abc')
into try_x values (4,1,2,'abc')
into try_x values (5,1,2,'abc')
into try_x values (6,1,3,'abc')
into try_x values (7,1,3,'abc1')
into try_x values (8,1,3,'abc2')
into try_x values (9,1,3,'abc2')
select * from DUAL;

Although a is the PK, records with similar b,c,f_text are considered redundant and I need to delete all occurrences in the table where b, c, d are redundant and leave the unique ones. So I need the result to look like:

a b c f_text
-----------------
0 1 1 abc
2 1 1 xyz
3 1 2 abc
6 1 3 abc
7 1 3 abc1
8 1 3 abc2

View 15 Replies


ADVERTISEMENT

PL/SQL :: Deleting Duplicate Records

Dec 20, 2012

I have a table and it's having duplicate records.

for one particular employee, he is having multiple records with the same data in the table

EMPNO    ENAME     JOB     SAL     DOB
-------------------------------------------------------
1               A           X        100     1956
2               B           Y        200     1974
1               A           X        100     1956
3               C           Z        300     1920

[Code]....

like this am having multiple times the duplicates.

I have written the below query to delete the duplicate records. But it is deleting only one record (if we have 5 duplicates it is deleting only 1 ). But I am looking to delete if we have 5 duplicates need to delete 4 duplicates and keep 1 record in the table.

query which am using to delete the duplicates is

DELETE FROM Table1 a 
WHERE ROWID IN (SELECT MAX(ROWID) 
                FROM Table2 b
                WHERE a.ID = b.ID); 

it is deleting only one row but I want to delete 4 records out of 5 and keep one record.

View 12 Replies View Related

SQL & PL/SQL :: Deleting Duplicate Combination Of Records From Table?

Sep 29, 2011

How can I delete the duplicate combination of records from the below table.

CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);

[code]....

The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.

Expected result after deletion

GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26

View 5 Replies View Related

SQL & PL/SQL :: Deleting Multiple Records Based On Conditions

Jun 28, 2010

I have a requirement where i need to retain latest 3 records based on creation date for each customer_id and delete the older records. The customer_ id or contract_number data in the test table are not unique.

Sample Table Script:

CREATE TABLE TEST
(
CUSTOMER_ID VARCHAR2(120 BYTE) NOT NULL,
CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
CREATION_DATE DATE NOT NULL
);
[code]...

View 8 Replies View Related

Forms :: Auto Populating Records In Other Fields Based On Inventory Number Field

Jul 13, 2011

I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.

How to set any triggers for the items to auto populate and i am suppose to finish this work today.

View 2 Replies View Related

Deleting Huge Records From Table?

Apr 29, 2013

Consider tables A,B,C,D,E,F. all are having 100000++ records Tables B,C,D are dependent on table A (with foreign key constraint). When I am deleting records from all tables, table B,C,D are taking max 30-40 seconds while table A is taking 30-40 mins. All tables are having indexes.

Method I have used:

1. Created Temp table

2. then deleted all records from B,C,D,E,F for all records in temp table for limit of 500.

delete from B where exists (select 1 from temp where b.col1=temp.col1);

3. why it is taking too much time for deleting records in table A.

View 5 Replies View Related

Forms :: Deleting Records From Block

Aug 2, 2011

I want to delete records from block in the form.could you explain where(in which trigger) should i write set_block_property.

View 9 Replies View Related

SQL & PL/SQL :: Delete Statement Is Deleting 1200000 Records

Sep 7, 2010

I executed the following delete statement.

DELETE FROM sre_t WHERE TO_CHAR(end_dt,'yyyy')<'2000'
or TO_CHAR(start_dt)<'yyyy')<'2000';

It's executing for 15 to 20 minutes after that i got the error "session timed out"..The table is having four crore records.The delete statement is deleting 12,00000 records.

View 4 Replies View Related

SQL & PL/SQL :: Performance Of Varchar2 Fields Based On Width?

Nov 10, 2010

I did a search on this topic and did see the ASK TOM response that storing all varchar2 fields as (2000) or what not is a bad idea based on an array fetch that developers may use etc. However I'm not sure that applies to my specific question, and the other examples he gave certainly didn't apply. So I'll pose the question a different way:

Question #1: Is there, for example, a performance difference between setting a field as varchar(2000) and varchar(25) if I was just running a native SQL query using a front end tool like TOAD?

Question #2: If I also need to index that field, will it take longer to index a varchar(2000) field than a varchar(25) field, assuming the same data is in both fields?

View 2 Replies View Related

SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source

Jun 1, 2010

I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.

TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....

View 9 Replies View Related

SQL & PL/SQL :: Avoiding Repetition Of Condition In Not Exist Clause?

Dec 7, 2010

Given two tables in a 1:N relationship, I want to retrieve, for each parent row, any child row which either satisfies some condition cond1 or, if no child rows matching cond1 exist, which satisfies another condition cond2.

I can easily write such a query using NOT EXISTS, but I am forced to repeat the condition cond1. This is inconvenient, as the repeated condition is quite long (involving other tables as well), and I would like to write it only once.

For example (using the HR schema included in Oracle 10g XE): I want to retrieve, for each department, all the "new" employees (hired after a certain date) or, if no "new" employees exist, the department manager.

SELECT d.department_id,
d.department_name,
d.manager_id,
e.employee_id,
e.first_name,
e.last_name,

[code].....

View 3 Replies View Related

SQL & PL/SQL :: See Only Duplicate Records But Not Able To Get Through?

Dec 22, 2010

i am using this query to see only duplicate records but not able to get through

select * from
emp
where rowid NOT IN
( select max(rowid) from emp GROUP BY job_id)

View 7 Replies View Related

SQL & PL/SQL :: Write Query To Delete Similar Records In Particular Fields (columns) In Different Tables

Jul 17, 2012

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

View 2 Replies View Related

SQL & PL/SQL :: Finding Duplicate Records

May 20, 2010

i have table with name, count, flag with dublicate records

example

with swayam name , counts are 3, 4
with ramana name, counts are 5,5
with reddy name, counts are 1,2,3

i want to update the flag

if count are same then update one of record (flag='A') and other should be flag='R'
if count are different then update the max count (flag='A') and other should be reject remaing (flag='R'). use below quires

CREATE TABLE TEST_DUB ( NAME VARCHAR2(99), V_COUNT NUMBER, FLAG VARCHAR2(1));

Insert into TEST_DUB (NAME, V_COUNT)
Values
('SWAYAM', 3);
Insert into TEST_DUB
(NAME, V_COUNT)
Values

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Duplicate Records And Group By

Jun 22, 2010

My duplicate records have been detected by First Name, Last Name, Name, and City.

such as

select FirstName, LastName, Name, City, count(*) as Num of Duplicate from TABLE
GROUP BY FirstName, LastName, Name, City
having count (*) > 1

It gives the duplicate record. Now I need all the columns and the each duplicate record in the select, so I can see why these records are duplicate.

View 6 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records

Sep 29, 2010

say I have duplicate records in TABLE3, can I use following SQL to delete the duplicates?FYI, BUS_FID is unique number column.

TABLE3
------------------------
ori_id*************id***********r_f_a****r_t_a*******bus_id
72840000040572*****740040572****255******267*********1
72840000040572*****740040572****255******267*********2
2840000040572******20040572*****25*******27**********3
7840000040572******70040572*****2********6***********4

DELETE FROM TABLE3 WHERE BUS_ID NOT IN (SELECT MIN(BUS_ID) FROM TABLE3 GROUP BY ORI_ID);

consider TABLE3 has millions of millions of data, say 40 millions.Update, I have tried it and it is very slow...

View 5 Replies View Related

SQL & PL/SQL :: Finding Duplicate Only Records In More Than One Column?

Dec 16, 2010

I am trying to write SQL which finds records which are duplicated in more than one column.

Requirement : When ever i have duplicates in Col2 and Col3 both i need that record...

My Source table:

COL1COL2COL3COL4
163kg87
263fh87
1ab23
2ab24
3cd98
4fg87
5xy77
6xy67

Desired Output

COL1COL2COL3COL4
1ab23
2ab24
5xy77
6xy67

For Table generation

CREATE TABLE TEMP_TEST
(
COL1 NUMBER,
COL2 VARCHAR2(10 BYTE),
COL3 VARCHAR2(10 BYTE),
COL4 NUMBER
)

Insert statements

Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
(163, 'k', 'g', 87);
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Query To Update Duplicate Records?

Sep 20, 2011

The requirement is I have a table (TAB1), wherein I have 3 columns, ID, LID and STATUS.

The value in ID column = ID_SEQ.NEXTVAL,and LID will be either 0 or 1 and the possible values for STATUS are 'ED','CP', NULL. The ID column is not suppose to have duplicate values, but there is no check on the table for the same.

Someone has updated the existing data and ID column is containing duplicate values. Wherever LID = 0 and STATUS = NULL and if only if ID is duplicated then the ID_SEQ.NEXTVAL has to be assigned to ID field, so that there are no more duplicate values.

CREATE TABLE tab1 (id NUMBER , lid NUMBER, status VARCHAR2(10));

Existing Data
------------------
INSERT INTO tab1 VALUES (1,0, 'ED');
INSERT INTO tab1 VALUES (1,0, 'CP');
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);

[code]....

get the result using a single update statement.

View 5 Replies View Related

SQL & PL/SQL :: Ranking Function And Duplicate Records

Aug 30, 2012

I have the following table :

CREATE TABLE A_TEST (A INTEGER, B INTEGER, C INTEGER, D INTEGER, FLAG CHAR(11));

INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(7,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(9,2,3,4);
[code]....

I would like to perform an update on the FLAG column by setting to "D" if it is a duplicate record.1,2,3,4);I would like to use the rank function.

Desired update:

A B C D FLAG
1 2 3 4
2 4 5 8
1 2 3 4 D
2 4 5 8 D
7 2 3 4
9 2 3 4
7 2 3 4 D
1 2 3 4 D
5 4 5 8
2 2 3 9
2 4 5 8
6 2 3 4
1 3 3 4
8 2 8 4

View 3 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records From A Table

Apr 13, 2011

I need to delete duplicate records from a table (indeed they are multiple duplicates).

Table Data

IDGroupQty
1KK30
1KK0
1KK19
2AA0
2AA30
3AA0
3AA30
3AA30
3AA9

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.

View 10 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records From Table

Aug 30, 2010

How can i delete duplicate records from the table.

View 2 Replies View Related

SQL & PL/SQL :: How To Display Data Without Duplicate Records

Nov 16, 2011

how to display the data which is shown below without duplicate records in compid and compname and all policy_id's should be there while excuting this query iam getting this data.

select distinct comp_id as compid,
comp_disp_name as company,
plcy_id as policyid,

[Code]....

output
-----------------
compid compname policy_id policy_name

19734 Save the Children 9013 GPA
19734 Save the Children 9012 GMC
20097 JMT 9486 GTL
10890 Steelco Gujarat Ltd. 9727 CAR
17330 Golden Jubilee Hotels Limited 8915 CGL
23117 NBHC 9093 GMC
17542 Heinz India 10693 Fire
19821 KSK Fabricators 10341 D&O
3769 Jones Lang Lasalle India 9199 WC
19821 KSK Fabricators 10340 WC

View 10 Replies View Related

Forms :: Restrict Duplicate Records

Jul 18, 2011

there is non database data block of table type.

now i want restrict user that he will not enter same number again.

View 3 Replies View Related

PL/SQL :: How To Count Duplicate Records In A Table

Jun 15, 2012

query to count the duplicate records and to display duplicate records in a table. Iam using oracle 10g.

View 2 Replies View Related

SQL & PL/SQL :: Oracle 9i / 10g - Remove Duplicate Records?

Nov 11, 2010

I have a query which pulls data from emp view

select am_obj_emp_obj(empno,ename,sal,deptno,service) from
(select empno,ename,sal,deptno ,service from emp_vw where 1=1 and rownum < 2000 and service in ('MAN','SACH','SACL','KACL'))
WHERE SAL = (SELECT MAX(SAL) FROM EMPS_VW WHERE DEPTNO = deptno or sal is null)
union all

[code]...

This query is pulling 12 records of which 6 records are coming from the first query and the same 6 records is coming from the second query after union all Here am_obj_emp_obj is the object type and emp_vw is the view

Now I wanted to remove the duplicate records.When I implement the union operater it is giving me error.

ORA-22950: cannot ORDER objects without MAP or ORDER method.

View 5 Replies View Related

PL/SQL :: Rank Function And Duplicate Records

Aug 30, 2012

I have the following table :

CREATE TABLE A_TEST (A INTEGER, B INTEGER, C INTEGER, D INTEGER, FLAG CHAR(11));

INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
[code].......

I would like to perform an update on the FLAG column by setting to "D" if it is a duplicate record.1,2,3,4);

I would like to use the rank function.

Desired update:
A     B     C     D     FLAG
1     2     3     4     
2     4     5     8     
1     2     3     4     D
2     4     5     8     D
7     2     3     4     
9     2     3     4     
7     2     3     4     D
1     2     3     4     D
5     4     5     8     
2     2     3     9     
2     4     5     8     
6     2     3     4     
1     3     3     4     
8     2     8     4     

View 5 Replies View Related

SQL & PL/SQL :: Merge Two Tables Into One And Knock Out Duplicate Records?

Jul 12, 2006

I have two identical tables with same structure and have some duplicates between those two. Finally I want to combine togather and in a single table and knockout the duplicates.

SQL how can we do this in a simple way? each tables has around millions records.

View 4 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records Without Rowid And Drop

Apr 7, 2012

I want to delete the duplicate records from a table without using Below 2 methods:

1> delete from table_1 a where row_id not in (select min(row_id) from Table_1 b where a.PK=b.PK);

2> Insert into Table_2 select distinct * from Table_1;
Drop table Table_1;
Rename Table_2 to Table_1;

View 20 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records Using Analytical Function

Dec 22, 2012

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.

My table(tab2) Structure is

DEPTNODEPT_NAMEEMPIDSEXID1
107jadf 1F1
40asdf 55
10purchase 2M2
10sales 3M3
30HR 4F4

I found the Duplicate Record by using the query

with a as
(select deptno,dept_name,empid,sex,id1,row_number()over(partition by deptno order by deptno) rnum from tab2)
select * from a where rnum >1

how to delete duplicate record .

View 19 Replies View Related

JDeveloper, Java & XML :: Duplicate Records After XML Extract

Mar 5, 2011

I've got a table with XML code (CLOB column) and I need to read and write every of parameter(blue marked below) and value (red marked below) record from that.

For example:

create table tmp_mape
(msisdn varchar2(100),
xml_params CLOB )
insert into tmp_mape values
('423903200200',
'<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>410</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>' );
[code]........

I tried to read this every values of xml code by select below:

select
extractValue(value(x),'/parameter/@name', 'xmlns="http://datalan.sk/webreporting/params/v1_0"') b,
extractValue(value(x),'/parameter/value', 'xmlns="http://datalan.sk/webreporting/params/v1_0"') a,
a.*
[code]......

This select returns a duplicate records because of more the one parameter and value records from XML column.

View 30 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved