I am trying to remove duplicates from a table with over 10million records. Below query is working fine but it doesnt contain any COMMIT interval. I have to commit after every 20k or 30k records deletion for which IF loop is necessary.
Query:
delete from
customer
where rowid in
(select rowid from
(select
rowid,
row_number()
over
(partition by custnbr order by custnbr) dup
from customer)
where dup > 1);
db and dev 10g rel2 ,suppose that i have a table with a lot of duplicate rows ,what i need is to delete the duplicates and retain one row of these duplicates . likecolumn -- with those values...how to delete two (hi's) and retain the third , ?it is all applied to all the duplicate values in the column.
View 5 Replies View RelatedI have a table like below:
tableA
aid des
1 concrete
2 wood
3 straw
4 plastic
5 fiber glass
6 other
tableB
bid material
01 1
02 2
03 3
01 2
01 2
02 3
01 5
The result I need when updating another table with this info is:TableC
edw_id bid Requirement
021 1 concrete, wood, fiber glass
032
025
123
521
I do not want :
concrete, concrete, concrete, wood, wood, fiber glass
SO far I am using the following but since I am dealing with hundreds of column that has the same material, when using listagg() from oracle 11.2g, they column width is too wide to fit into the required column.
update eris_data_work e set E.flex37 =
(select
LISTAGG(CM.des, ',') WITHIN GROUP (ORDER BY CM.des) AS casing_material
from CODE_CASING_MATERIAL CM, TBLCASING CA
where CM.code=CA.MATERIAL and CA.well_id=E.owner_oid AND CM.DES IS NOT NULL
GROUP BY CA.well_id)
where E.source='WWIS_ON'
I have even used the regexp_count() to try to eliminate duplicates however I have had no success so far
I have a table1:
userid, name, town.
Now i want to do a self join like:
select a.name, b.name, a.town
from table1 a
inner join table1 b
on a.town = b.town
where a.first_name <> b.first_name AND
a.last_name <> b.last_name
i added the where clause to limit duplicates i would get but i still get duplicates eg. A B london, B A london etc.
I have to load 50 Million records in a table. I also need to ensure that no duplicate records enter. Business demand is that It may send 2 Million records today, 1 Million records tomorrow, 3 million records on the next day and so on...
I have loaded 6 Million records into the table, but onwards speed of loading records (with duplicate check) is decreasing.
I have need, where I need to pick-up the one of the records out of 2, which have all the fields same but one key column? how should I do it?
sample data:
IDnamecreation dateupdate date
121233ATR7/19/2010 15:307/19/2010 15:30
1213344MTR7/19/2010 15:307/19/2010 15:30
For most of the cases, i am picking latest data using update date, but here where dates are same, I am stuck.
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10))
insert into testingtype_table values (1124,2424,'cbaaa');
insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10));
create or replace type testingtype_tab as table of testingtype_obj;
Code Block
declare
l_testingtype_tab testingtype_tab := testingtype_tab();
begin
select distinct testingtype_obj(ordernumber
,org_id
,company_name)
bulk collect into l_testingtype_tab
from testingtype_table;
end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method
Removing duplicates from a string that contains years and "-".
Example: 1988-1997-2000-2013-1998-1965-1997-1899
I know this can be done in regular expressions but have no experience in this subject.
select REGEXP_REPLACE(.....) from dual;
i have two tables
Quote:
Table has following data.
id indicator
---------------
1 A
2 A
3 A
Quote:
Table2 has
id indicator
---------------
1 X
1 X
2 X
3 X
I would like to have following output ( Am running query on Toad)
t1_id t1_indicator t2_id t2_indicator
---------------------------------------------
1 A 1 X
1 X
2 A 2 X
3 A 3 X
I have a requirement with source table has duplicates so i need to select a row based upon the below condition.
Source table
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50
Desired Output
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50
I tried with MAX and DENSE_RANK but its not getting me the first row.
I ran an Inner Join but it returned many, many duplicates. One Answer from a different thread:Because you may be having improper Join condition what has a One to many or a Many to many relationship between the tables. We do not have the data, and hence cannot comment over it. What do I look for to identify an improper join?
View 2 Replies View RelatedI have a requirement with source table has duplicates so i need to select a row based upon the a condition.
Source table
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50
Desired Output
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50
I tried with MAX and DENSE_RANK but its not getting me the first row.
I want to remove duplicates from a column MAIN_TABLE based on TLEVEL Column:
create table UNIQ_TEMP
( TLEVEL NUMBER(10,0),
TABLE_NAME VARCHAR2(30),
MAIN_TABLE VARCHAR2(30)
);
[code]....
My Requirement is:
MAIN_TABLE= MARKETING_OPTIN
EXISTS IN MAX(TLEVEL)
REMOVE DUPLICATE MARKETING_OPTIN FROM OTHER LEVELS
This should apply to all the values in column MAIN_TABLE
I'd like to achieve the following (and YES, I do know that this is not multi-user safe, but that's not the point here):
Before inserting a record, the trigger shall check if there's already a duplicate one. Duplicate means in this case when there is an intersection of the time frame, defined by two numeric timestamps. That's also the cause why I cannot use a simple UNIQUE constraint here (in my opinion).
Okay, that already works (see code below). But now I need colliding records to be written to a temporary table so that those records can be returned and presented to the user for selection.
create or replace
TRIGGER TRIGGER1
BEFORE INSERT ON FLIGHT_TABLE
FOR EACH ROW
BEGIN
FOR fs_entry IN (SELECT * FROM FLIGHT_TABLE)
LOOP
[Code] .........
I have a table like this basic example:
ID Product Color Time-In
1 Apple Green May
2 Apple Red April
3 Pear Green May
4 Pear Green April
5 Plum Blue June
In SQL I want to return all 4 fields of the records except those records where Product and Color are identical - in that case it should return the latest (by name of month - preferred) or just the first it finds
So I should get these
1 Apple Green May
2 Apple Red April
3 Pear Green May
5 Plum Blue June
If I do a select distinct then I will only get those fields I test on (product and color), not the rest.
I am trying to delete duplicates from table without using rowid.
here is data:-
create table test(col1 number(3),col2 varchar2(20));
insert into test values(100,'rocky');
insert into test values(100,'rocky');
[Code]....
I know i am perfoming dml on view. IT wont allow me to perform DML on view which contain columns with expression. IS there any way to delete duplicates without using rowid?
I am trying to work with query to get distinct unique records, following is my scenerio:
DocNo |Title| Revs|UpdateTime|OBID
DOC-101|DESCRIPTION1|1|2/28/2004 11:37|6108-9
DOC-101|DESCRIPTION1|1|2/28/2004 11:36|6106-13
DOC-201|DESCRIPTION2|0|4/24/2005 16:44|7899-1
[code]...
From the above duplicates I would like to get only 1 distinct record based on latest update time (when using distinct on a sub-query since OBID is unique again returning all recds)
I am expecting results like:
DocNo|Title| Revs|UpdateTime|OBID
DOC-101|DESCRIPTION1|1|2/28/2004 11:37|6108-9
DOC-201|DESCRIPTION2|0|4/24/2005 16:47|7900-1
DOC-301|DESCRIPTION3|3|2/21/2007 7:26|6869-4
DOC-304|DESCRIPTION4|3|8/22/2007 9:31| 39208-1
I have a query like
SELECT country_name,
substr(SYS_CONNECT_BY_PATH(product_name,','),2) as PRODUCT_NAME,
substr(SYS_CONNECT_BY_PATH(SPEED_VALUE,','),2) as SPEED_VALUE,
substr(SYS_CONNECT_BY_PATH(i.SUPPLIERNAME_ACCESSPROTYPE,','),2) as SUPPLIERNAME_ACCESSPROTYPE
FROM (SELECT b.country_name,b.product_name,b.speed_value,(supplier_name|| supplier_product || access_product_type)as
[code].......
In the result , I am getting repeated values for product_name and speed value,something like 'ALL Products,All Products,All Products'in the product_name column and '128Kbps,128Kbps'in Speed_vale.i am not able to remove the repeated values here.
I have created a table and i have a column consisting of 1000 records (but where i have duplicates). And now i want to create a primary key for the column.
View 7 Replies View RelatedHow to eliminate duplicates from record types?Below code errors out with "Wrong number of arguments in call to MULTISET...."
error. DeclareTYPE ln_x_tab IS RECORD(x1 number ,x2 VARCHAR2(4000) ,x3 VARCHAR2(4000) ,x4 VARCHAR2(4000) ,x5 VARCHAR2(4000)); TYPE ln_x_type IS TABLE OF ln_x_tab INDEX BY BINARY_INTEGER; ln_x1 ln_x_type; ln_dist_x1 ln_x_type; gc_stmt varchar2(4000); Begin gc_stmt := ' SELECT x1, x2, x3, x4, x5 FROM table WHERE dynamic_conditions; EXECUTE IMMEDIATE gc_stmt BULK COLLECT INTO ln_x1; ln_dist_x1:= ln_x1 MULTISET UNION DISTINCT ln_x1; End;
I need ln_dist_x1 to have distinct records from table.
I have to write a file using the UTL_FILE that needs to look like the one below. I am getting the righ results but I am getting duplicates, If I have more than one records retrieve in the Invoice_data_cur cursor(for the same pidm) I got the right results, but I got duplicate data, in another words if I retrieve 3 records from the Invoice_ data_cur, I got the same record write to the file 3 times, but the right results. If I only retrieve 1 record in Invoice_ data_ cur , I only get the results of the in invoice_detail_charges_cur I don't get the results of the invoice_ detail_ payments_ cur:
H xxxxxxxxxxxx
I 00399999 10/02/201000000000005000000000000500Jane Smith
D 00366899 Current charges Fall 201010/02/2010Study Abroad Fee - Semester 0000000001200
D 00366899Current charges Fall 201010/02/2010France School Abroad 0000000011200
[code]....
I am getting data in all the cursors..
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.
To remove fragmentation which is the best method.
First one :
-----------------
1)Created a backup table from the Fragmented table (This table is a partitioned one).
2)Analyzed this table.
3)DROP the Fragmented table
4)Inserted the backuped up data from backup table to the Re-created table.
5)Analyze this table.
Second method
-------------------
1) Create a backup table newly, with PCTFREE =0
2) Inserted the data from Fragmented table ( This is a partitioned table) to backup table
3) Analyzed this table.
4) Truncate Fragmented table
4) Did Exchange partition of Fragmented table with Backup table.
The second method is not found to be removing the Fragmentation. Before the fragmentation was 28% after Second method the fragmentation is still the same. While the first method the fragmentation reduced to 16%.
Query used to find Fragmentation.
select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
[code]...
TYPE CashRecord IS RECORD(client_id VARCHAR2(100),
account_letter VARCHAR2(100),
cash_amount VARCHAR2(100),
cash_amount_ccy VARCHAR2(100) );
TYPE CashRecordTable IS TABLE OF CashRecord INDEX BY VARCHAR2(100); -- Indexed by client_id~account_letter~ccy
So if I did something like this;
l_cash_records CashRecordTable;
-- say for example that l_cash_rec/l_cash_rec2 has been defined..
l_cash_records('some index') := l_cash_rec;
l_cash_records('some index 2') := l_cash_rec2;
l_cash_records.COUNT would give me 2
How can I somehow remove 'some index 2' so that l_cash_records.COUNT is 1 ?
This is a simple question, but I cannot seem to find a solution. Here's the basic query:
select distinct accountno, parcelno, streetno||' '|| predirection ||' '|| streetname||' '|| streettype||' '|| postdirection||' '|| unitname||', '|| propertycity
from tblacctpropertyaddress ....
What I want to do is add is this logic: If Predirection is null, then no space between streetno & streetname. Same for postdirection and unitname. (for example, if both postdirection and unitname are null, there are no spaces between streettype and the comma before propertycity)
Also, when unitname is not null, I want to add the string "Unit " prior to the returned value in unitname.
One of my clients need to remove three(of four) CPU to comply the licensing agreement with Oracle.
To avoid problems and also to list the possible problems that removing the CPU can bring, I wish to make a survey of the possible impacts, especially in performance, that removal can cause.
How can I get this information?
I am working with Oracle 10G, and have been working on setting up little pl/sql checks to make sure that the data that is imported is in the correct format.
The wall I have hit is removing illegal characters from the data I import. I have started to set something up where the string for a certain column must be be made of only there characters:
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz-" (note that there is a - besides just letters) and I may want to add some other characters later. So basically the script will drop or replace any character not found in my definitions with "", thus removing the illegal character and joining the previous and next characters.
I thought for sure there would be a script posted somewhere online that did this but I can't find it and my syntax skills are lacking.
I have this query that returns results that contain duplicates(somewhat). I only want either the FIRST or LAST (either one is fine). Here is the query:
select unique PLLA.attribute4, PLA.item_description from po_lines_all PLA, po_line_locations_all PLLA
where PLLA.po_line_id = PLA.po_line_id
and PLLA.attribute4 is not null
So my output is something like this:
RCE12 This is an item for AUL1
RCE13 This is an item for PWEILL
RCE14 This is an item for AUL1
I just want either the RCE12 or RCE14 record and not both since they both have the same description.
I've got a slowly changing dimension table for products with some duplicate attributes -
UNIQUE_ID | DATE_FROM | DATE_TO | PRODUCT_ID | ATTRIBUTE_1 | ATTRIBUTE_2
1 01-JAN-13 02-JAN-13 423 MONKEY 5
2 03-JAN-13 04-JAN-13 423 MONKEY 5
3 05-JAN-13 08-JAN-13 423 MONKEY 4
4 09-JAN-13 10-JAN-13 423 SUPERMONKEY 4
5 01-JAN-13 08-JAN-13 378 BANANA 2
6 09-JAN-13 10-JAN-13 378 BANANA 3
The natural key should be PRODUCT_ID, ATTRIBUTE_1 and ATTRIBUTE_2. The table should therefore be recreated as follows:
UNIQUE_ID | DATE_FROM | DATE_TO | PRODUCT_ID | ATTRIBUTE_1 | ATTRIBUTE_2
1 01-JAN-13 04-JAN-13 423 MONKEY 5
2 05-JAN-13 08-JAN-13 423 MONKEY 4
3 09-JAN-13 10-JAN-13 423 SUPERMONKEY 4
4 01-JAN-13 08-JAN-13 378 BANANA 2
5 09-JAN-13 10-JAN-13 378 BANANA 3
I have a view that is displaying records that are almost duplicates with the exception on one column.
Distric name id status logtime
------- --------- ---- ------ ---------------
ATLANTA John 007 A 4/5/2011 8:20
ATLANTA John 007 B 4/5/2011 8:20
ATLANTA John 007 D 10/5/2012 8:20
JERSY C. Eric 111 C 8/1/2012 4:45
PLANO Jake 522 A 1/2/2012 7:22
DENVER Steve 222 C 11/24/2012 9:00
DENVER Steve 222 D 11/24/2012 9:00
I have a situation where I need to only display one status per name at a specific time.I would like to return the following:
Distric name id status logtime
------- --------- ---- ------ ---------------
ATLANTA John 007 A 4/5/2011 8:20
ATLANTA John 007 D 10/5/2012 8:20
JERSY C. Eric 111 C 8/1/2012 4:45
PLANO Jake 522 A 1/2/2012 7:22
DENVER Steve 222 C 11/24/2012 9:00
So I decided to include a rank for the different statuses to identify the highest rank status and eliminate the lowest rank status records I don't want.
Select distric,name,id,status,
case status
when 'A' then 1
when 'B' then 2
when 'C' then 3
else 4
end statusrank,
logtime
from tst;
[code]...
I would like to return the record with the highest statusrank when they duplicates with the exception of the status column.Is there a built in function I can use for this ? The statusrank can be returned in query. My final return would be:
Distric name id status statusrank logtime
------- --------- ---- ------ ---------- ---------------
ATLANTA John 007 A 1 4/5/2011 8:20
ATLANTA John 007 D 4 10/5/2012 8:20
JERSY C. Eric 111 C 3 8/1/2012 4:45
PLANO Jake 522 A 1 1/2/2012 7:22
DENVER Steve 222 C 3 11/24/2012 9:00