SQL & PL/SQL :: Duplicate Row Elimination

Oct 7, 2013

for eliminating duplicate rows in a table, how to write an sql query?

View 4 Replies


ADVERTISEMENT

Duplicate Database Name

Apr 25, 2012

I have two questions:

can i have two databases with exact names - but in different oracle home? will oracle let me create two identical database name in same oracle home?

View 2 Replies View Related

SQL & PL/SQL :: Identify Duplicate Row

Feb 1, 2011

I have table named TEST with NO (NUMBER) and DOJ (DATE) columns with primary key.

Post inserting a millions of records to it, i am inserting a duplicate NO and DATE.

How oracle identify (mechanism used) the latest record as duplicate?.

View 4 Replies View Related

SQL & PL/SQL :: Getting Duplicate Results

Feb 7, 2012

I am getting duplicate results..I have been at it for hours now.This is what I have:

DECLARE
v_course_id classes.course_id%TYPE := :course_id;
v_instr_id classes.instr_id%TYPE := :instructor_id;
[code]...

The output I get is this (There is only 1 of each in the database)

Class ID: 1 Status: Enrolled Name: John O'Reilly
Class ID: 1 Status: Enrolled Name: Natacha Hansen
Class ID: 1 Status: Enrolled Name: Reed Jetto
Class ID: 1 Status: Enrolled Name: Janis Greenberg
Class ID: 1 Status: Enrolled Name: Vishal Singh
Class ID: 1 Status: Enrolled Name: Francis Hamilton
[code] ...

View 2 Replies View Related

SQL & PL/SQL :: Duplicate Value In Table

Dec 15, 2010

I have a following table,

create table ashok_temp11(col1 varchar2(10),col2 varchar2(10));

and have the following values,

insert into ashok_temp11 values('A1','1001');
insert into ashok_temp11 values('A2','1001');
insert into ashok_temp11 values('A3','1002');
insert into ashok_temp11 values('A4','1003');

[Code]...

I need the duplicate values in the table. That means

col1 col2
a1 1001
a2 1001
a6 1001
a3 1002
a7 1002

I tried the following query and its not fetching me any records.

SELECT col1,
col2
FROM ashok_temp11
GROUP BY col1,
col2
HAVING COUNT(col2) > 1
ORDER BY col2

View 11 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

Duplicate Database To Another Host

Mar 10, 2013

I will be duplicating my production database back to a few weeks ago on my test server, to retrieve certain data. I am running 11g standard edition, not using a recovery catalog.

Below are the basic steps I plan to take in a nutshell.

1. Copy the backup files to the test system

2. In the rman run block (on the test server)
A. Set newname for each of the datafiles and tempfiles (don't have many files)
B. Duplicate command to point in time, specifying backup location and no filename check.

3. Export the data

View 6 Replies View Related

Eliminate Duplicate Rows

Jan 27, 2009

I have to eliminate duplicate pairs from the following data set.

for ex. Adney Vaughan and Berkly Wassen appears in both AG1 and AG2. how can i get rid of these repititive rows?

AG1 ----------- AG2
Acton Wibert ---- Currier Barhydt
Adney Vaughan --- Luella Edmund
Adney Vaughan --- Berkly Wassen
Alden Anstruther --- Courtney Gavet
Ashley Alvord --- Saunders Buel
Aswin Wilbraham --- Dale Cooper
Barnum Sears --- Grayson Lightfoot
Berkly Wassen --- Luella Edmund
Berkly Wassen --- Adney Vaughan
Bersh Musgrave --- Derward Knight
Berthilda Darrell --- Broderick Reynold
Broderick Reynold --- Berthilda Darrell

View 1 Replies View Related

Removing Duplicate Keys

Jul 3, 2008

Trying to delete duplicate rows from a table. The problem is, they aren't exactly duplicate rows. Let me explain.

I am migrating data from a Oracle 8.1.7 db to a 10.2.1 db. In the older db, this certain table does not have a PK/Unique Index, but in the new db there is a unique index. The fields that the index is unique on are:

SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE.

In the old db, when I run this query I get 1229 rows. With a count of 2 each.

select SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE, count(*)
from customer_id_equip_map
group by SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE
having count(*)>1;

They are duplicates on those fields, but they are not totally duplicate rows because there is a field called is_current that has 0 in one row and has 1 in the other. What I need to do, is delete the 1229 rows with is_current=0.

View 4 Replies View Related

Delete Duplicate Rows?

Feb 6, 2011

I having a problem with duplicate rows with a query.

What I have so far is

SELECT D.Student_Id,
E.Latest_Reg_Date,
E.Attendance_Count,

[Code].....

View 1 Replies View Related

SQL & PL/SQL :: Get Duplicate Values Using Delimiter

Sep 1, 2010

I have table with below values

SELECT * FROM dup_val

1
1
1
1
2
2
2
2
3

The result set should be like

1-1
1-2
1-3
1-4
2-1
2-1
2-2
2-3
3-1

the query get the above result set.

View 10 Replies View Related

SQL & PL/SQL :: Function To Duplicate The Word

Apr 13, 2012

function to duplicate the word

example member

result will be meber

View 11 Replies View Related

Forms :: Remove Duplicate Value

May 28, 2012

how can i get distinct records through this coding, when i add select distinct col1, col2, col3 from tablename where RECD_ON between :control. REC_ FROM and :control.REC_TO; in 1st qry after begin, this form not retrive any data from database, then how i get distinct rows through this coding. is there any option in property plattee to get distinct rows.

declare
qry varchar2(5000);
n number;
alert number;
Begin
[code]....

View 8 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 Values In Two Columns?

Apr 25, 2011

How to find the duplicate values in two columns.

Suppose we have two columns A and B and the data looks like this

A B
--- ---
1 One
2 Two
1 One
2 Two
3 Three

I need to write a query in such a way that i should find out the duplicate values which are repeating.

View 7 Replies View Related

SQL & PL/SQL :: Find Out Duplicate Name From Table?

Oct 28, 2010

i have a employee table .i want to find out duplicate name from this table.so i write following query

select empname from employee
having count(empname) >1
group by empname

i find the result but problem is how i can find out the same name if it have any space like ramkumar ram kumar both are same but above query did not.give this data. how i can solve it

View 16 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 :: How To Get Duplicate Record In Table

Aug 7, 2012

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.

View 1 Replies View Related

SQL & PL/SQL :: Update Duplicate Rows To 0?

Oct 25, 2011

getting sql query to get the result below.If the Key repeats then I need to set it to O if for each key New_link doesnot match.

My Present table

Key New_Link
1 4
3 2
3 5
5 1
5 1

RESULT

Key New_Link
1 4
3 0
5 1

View 5 Replies View Related

PL/SQL :: Duplicate Rows Removal

Jul 3, 2012

removing duplicate rows from a table.

We have the following table:

SSD@ermd> desc person_pos_history
Name Null? Type
------------------------------------------------------------------------ -------- ------------------------

PERSON_POSITION_HISTORY_ID NOT NULL NUMBER(10)
POSITION_TYPE_ID NOT NULL NUMBER(10)
PERSON_ID NOT NULL NUMBER(10)
EVENT_ID NOT NULL NUMBER(10)
USER_INFO_ID NUMBER(10)
TIMESTAMP NOT NULL DATE

We found out that few person_id's are repeating for a particular event (3):

select PERSON_ID, count(*)
from person_pos_history
group by PERSON_ID, EVENT_ID
having event_id=3
and count(*) > 1
order by 2

PERSON_ID COUNT(*)
---------- ----------
217045 356
216993 356
226198 356
217248 364

[Code]...

If we look at the 1st person id "217045", we can see that it is repeating 356 times for event id 3.

SSD@ermd> select POSITION_ASSIGNMENT_HISTORY_ID, POSITION_TYPE_ID, PERSON_ID,EVENT_ID, to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS')
2 from person_pos_history
3 where EVENT_ID=3
4 and person_id=217045
5 order by timestamp;

PERSON_POSITION_HISTORY_ID POSITION_TYPE_ID PERSON_ID EVENT_ID TO_CHAR(TIMESTAMP,'
------------------------------ ---------------- ---------- ---------- -------------------
222775 38 217045 03 2012-05-07 10:29:49
222774 18 217045 03 2012-05-07 10:29:49
222773 8 217045 03 2012-05-07 10:29:49

[Code]...

356 rows selected.It is safe to assume that the person id/event id with the earliest timestamp is the one that was loaded 1st, hence, the one we want to keep and the rest should be deleted.

sql to achieve the duplicate removal.

View 6 Replies View Related

SQL & PL/SQL :: Updating A Duplicate Primary Key

Oct 8, 2012

PM_KEY.. PCN....... JOBNO...PM_VERNO
20......... 137....... XX23..... 0
21......... 137....... XX24..... 1
22......... 137....... XX17..... 2
23......... 137....... XX81..... 3
22......... 137....... XX90..... 2

I have a dilemma......the constraint was disable somehow in my table of about 900,000 records which allowed the insertion on duplicate primary keys as well as duplicate records. I've managed to get rid of the duplicate records, but I haven't quite figured out how to update the primary key and version number. I've tried the following but

UPDATE TABLE
SET PM_PM_KEY=(MAX(PM_KEY)+1), PM_VERNO=(MAX(PM_VERNO)+1)
WHERE TBLDATE= MAX(TBLDATE)
ORDER BY TBLDATE ASC
GROUP BY PCN;

The query is failing with
Error at Command Line:2 Column:19

Error report:
SQL Error: ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:

View 6 Replies View Related

Forms :: 6i Duplicate Record?

Nov 4, 2013

I am using forms 6i, I want to insert a new record in the form based on an existing record (so I do not have to type all the values)

is there a shortcut key that I can use to copy the entire record and then when I say insert new, I just paste the record I just copied

View 4 Replies View Related

SQL & PL/SQL :: Duplicate Values Removal

Aug 6, 2010

Yesterday (05/08/2010) i have mistakenly inserted duplicate values in the tables, every value is inserted 2 times in a table.

so upto my knowledge the data were correct upto 30/Jul/2010. hence i need to recover the data from this 30th Jul date.

View 4 Replies View Related

SQL & PL/SQL :: Duplicate Record - Delete?

Apr 8, 2012

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.

View 14 Replies View Related

SQL & PL/SQL :: Divide One Of Column Value With Other As Duplicate

Feb 6, 2012

below are the create and insert statement, making duplicate row, i mean In table emp_detail, we have row like

ENAMEJOBDEPTNODETAIL
RAJ CS 10 RAJ IS IN CARE OF ROHIT

We need to insert the same record but divide the last column value on the basis of "IN CARE OF" any word after this should come in next new row with the same value for all other column, like

ENAMEJOBDEPTNODETAIL
RAJCS10RAJ IS IN CARE OF
RAJCS10ROHIT
CREATE TABLE "EMP_DETAIL"
("ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"DEPTNO" NUMBER(2,0),
"DETAIL" VARCHAR2(100)
[code]...

View 3 Replies View Related

SQL & PL/SQL :: Duplicate On A Single Column

Sep 23, 2010

I have a table with multiple columns and I need to retrieve the ones where column a stores duplications for records where column b is discrepant

this is the table

NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12345 +41 22 595 5555
12342 +41 22 595 5500
12340 +41 22 595 5555

the query should return

NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12340 +41 22 595 5555

The closest I got was with the below, but this also returns duplicates within the same NAME_ID.

select phone_number, name_id
from name_phone
where (phone_number) in
(select phone_number
from name_phone
group by phone_number
having count(*) > 1)
group by phone_number, name_id
order by phone_number

View 3 Replies View Related

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

PL/SQL :: Find Out Duplicate Record ID

Aug 14, 2013

There is more than one duplicate email id in the table. need to find out the duplicate record id.

View 7 Replies View Related

RAC & Failsafe :: RMAN Duplicate DB For RAC

Jan 26, 2004

We have a script which uses the duplicate functionality of RMAN. It was designed for a standalone database. Now I need to make it work for a cluster DB (RAC installation).

1. Does RMAN support this?
2. Do I need to make separate executions for both the nodes of the cluster?

We are currently blocked because we have no way of getting our system running without this!
******************************8
First attempt:

oracle@sf23> ./nsr_adv_duplicate_db.sh -d ADVFRW_sf25 -t ADVFRW1 -r RCAT -n sys -p sys ->

3808 11:52:49 ----- Starting Cloning Procedure -----
3808 11:52:50 INFO: Client host 'sf25' says, it is 'sf25'.
3808 11:52:51 Getting Original DB Name on the source host (referred by ADVFRW_sf25 TNS-name)...
...
3808 RMAN: GROUP 8 ( '/export/home/oracle/dev/ADVFRW/ADVFRW.redo181' ) SIZE 134217728 REUSE
3808 RMAN: DATAFILE
3808 RMAN: '/export/home/oracle/dev/ADVFRW/ADVFRW.system'
3808 RMAN: CHARACTER SET WE8ISO8859P1
[code]....

Second attempt:

oracle@sf23> ./nsr_adv_duplicate_db.sh -d ADVFRW_sf25 -t ADVFRW -r RCAT -n sys -p sys -c sf25 -s sf04 -g Srv_scl04-backup_DB_grp -l american_america.WE8ISO8859P1

5548 12:27:05 ----- Starting Cloning Procedure -----
5548 12:27:06 INFO: Client host 'sf25' says, it is 'sf25'.
5548 12:27:07 Getting Original DB Name on the source host (referred by ADVFRW_sf25 TNS-name)...
5548 12:27:07 Original DB is 'ADVFRW'
5548 12:27:08 INFO: BGROUP=Srv
[code].......

Now we have contrary error messages. Target DB name ADVFRW does not work, because on the target nodes there are configuration files for the node specific DB names (ADVFRW1.ora on first node resp. ADVFRW2.ora on second node). But with the node specific DB name the script fails, because it does not match with the DB name of the source system.

View 10 Replies View Related

Configuration Duplicate Session ID

Oct 29, 2012

I am getting a duplicate SID (ABC1 (2)) on UNIX. I have checked all the possibilities how to remove it but unable to find it. As you you see below:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORATAB:
================================================
ABC1:/oraclehome/oracle10g:Y
================================================
/oraclehome/oracle10g/bin/tnslsnr LISTENER_ABC1 -inherit
================================================

SID_LIST_LISTENFCATDR =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oraclehome/oracle10g)

[Code]...

Result:

ls -lad /orahome/ora10g/dbs/initABC1 (2).ora

ls -lad /orahome/ora10g/dbs/initABC1 (2).ora ; echo ;
/bin/sh: 0403-057 Syntax error: `(' is not expected.
--------------------------------------------------------------------------------------------------------------

what to need to do to avoid this duplication? Is it something wrong with Oracle Listener.ora as it have two listeners (ABC and ABC1) that might be causing duplication?

View 8 Replies View Related







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