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
ADVERTISEMENT
Mar 25, 2013
My table have duplicate records. I want to impose a primary key on that table. For that I have to replace duplicate values of a column with next value of a sequence.
View 2 Replies
View Related
Mar 6, 2013
create table test1
( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE
[code]....
insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 2012
View 10 Replies
View Related
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
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
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
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
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
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
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
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
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
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
Aug 30, 2010
How can i delete duplicate records from the table.
View 2 Replies
View Related
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
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
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
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
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
Mar 14, 2013
In a table i have some duplicate rows
I can get it through this query : select PARTY_ID from XXWFS_CUSTOMER_EXT group by PARTY_ID having count (PARTY_ID) > 1;
Now for the records which i got for each duplicate row i want to update the second row with a specific value.. so that duplicate rows does not exist anymore
Ex: I got party id's 12, 14, 16, 18 two times each
Now as 12 is two times.. i want to update the second row of 12 with some x value same is the case for other values like 14,16, etc
how can i write a procedure for this
View 3 Replies
View Related
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
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
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
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
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
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
View Related
Apr 9, 2010
I'm calling sql loader recursively to load data from CSV files which has thousands of records in each file. If there are any duplicate records, the sql loader terminates with ORA00001. My query is how to ignore inserting duplicate records and continue with the load.
Most of the posts in forums suggests to use skip command. But i do not think that is a wise option in my case as we cannot predict the maximum error count. more over I have set up ERROR=0 in my code so that the code terminates in case thers is a data error.
any other way to ignore inserting duplicate records into the tables.
View 7 Replies
View Related
Mar 7, 2011
when i run report in PDF, RTF or HTML then correct numbers of records appear but when i run same report in excel then it bring extra records (duplicate).
View 1 Replies
View Related
Sep 6, 2010
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
Query for anyone interested:
Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.
View 18 Replies
View Related
Sep 15, 2012
I have below query.. When i run this query i need to get two rows.. But i am getting two more duplicate rows.. I want to restrict these two rows..
How can i do this.. Here the problem is when i join B query then only i am getting duplicate rows..
SELECT DISTINCT B.TIC_ID, B.TIC_ISS_NO,
B.TIC_NUMBEC||CEV_ID,
B.TIC_NUMBEC, B.CEV_ID, B.AOSTED_DATE,
B.COMAANY_CODE, B.CONTCACTOC_NAME,
B.FC_CODE, B.C_NO, B.FC_TYAE,
[code].......
View 4 Replies
View Related
