PL/SQL :: Procedure To Update Duplicate Rows?

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


ADVERTISEMENT

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

SQL & PL/SQL :: Update Previous Row Based On Next Row And Update Duplicate Record

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

SQL & PL/SQL :: Update Statement With Join Only Update Matching Rows

Aug 17, 2010

This is my query:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c

[Code]...

The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds

[Code]...

but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.

View 9 Replies View Related

SQL & PL/SQL :: How To Update Multiple Rows With Different Values Using Update Statement

Mar 21, 2011

I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.

Eg:-

SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;

EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250

[Code]....

The above table contains 14 records. Now i would like to update the salary column with different values like

EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000

How to update above values with single update query.

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

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

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 :: 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 :: How To Remove Duplicate Rows From Table

May 8, 2013

how to remove duplicate rows from table?

View 6 Replies View Related

SQL & PL/SQL :: How To Retrieve Duplicate Rows From A Table

Jun 14, 2010

How to retrieve duplicate rows from a table, suppose i don't know any column names. Without hard-coding column names I need to find.

View 5 Replies View Related

SQL & PL/SQL :: Joining But Avoiding Duplicate Rows

Oct 16, 2010

I have two tables A and B

CREATE TABLE A(EMP_ID NUMBER, EMP_NAME VARCHAR2(100))
CREATE TABLE B(EMP_ID NUMBER, EMP_ATT1 VARCHAR2(10), EMP_ATT2 VARCHAR2(10))
INSERT INTO A VALUES(1, 'ONE');
INSERT INTO A VALUES(2, 'TWO');
INSERT INTO A VALUES(3, 'THREE');

[Code]....

This query returns all the matching row of A and B

SELECT A.EMP_ID, A.EMP_NAME, B.EMP_ATT1, B.EMP_ATT2
FROM A
INNER JOIN B ON A.EMP_ID=B.EMP_ID

The output for this shows:

EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
2 TWO 2ATT1.1 2ATT2.1
3 THREE 3ATT1 3ATT2

The requirement is to avoid duplicate rows even if matched:

EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
3 THREE 3ATT1 3ATT2

View 8 Replies View Related

SQL & PL/SQL :: Calculating Sum From Duplicate Rows With Timestamp?

Sep 16, 2011

I am trying to find sum for one record for each partition but while taking that timestamp giving me bit trouble, i have tried to reproduce the table and some little data

CREATE TABLE TEST_COUNT
(END_TIME DATE
,SUCCESSFUL_ROWS NUMBER
,FAILED_ROWS NUMBER
,TBL_NAME VARCHAR (4)
,PARTITION_NAME VARCHAR (240) )

[code]....

View 11 Replies View Related

SQL & PL/SQL :: Duplicate Rows Displayed For A Column

Mar 30, 2013

column sid format 'a5'
column serial# format 'a10'
column mins_running format 'a15'
column sql_text format 'a100'
set linesize 200
set pagesize 30

[Code]..

I am running this code, and the output shows multiple lines.

TRIM(S.SID) TRIM(S.SERIAL#) MINS_RUNNING SUBSTR(Q.SQL_TEXT,1,70)
---------------------------------------- ---------------------------------------- --------------- ----------------------------------------------------------------
700 46592 242.08 Select count(*) as count, case when count(*)>0 then 'FAIL' else
700 46592 242.08 'PASS' end as result
from (SELECT cv.code_value
FROM code_valu

[Code]...

Is there a way to wrap up the column for SQL_TEXT VARCHAR2(64) so that I can 1 row for the output?

View 14 Replies View Related

PL/SQL :: Running Total For Duplicate Rows

Dec 19, 2012

I am trying to write an sql which shows the running total for records which has duplicate.

sample rows:

col1 col2 col3
1      A    2
1      A    2
1      A    2
1      B    3
1      B    3
1      C    5
1      D    2
1      D    2o

p required:

col1 col2 col3  cumulative_tot
1      A    2       2
1      A    2       2
1      A    2       2
1      B    3       5
1      B    3       5
1      C    5       10
1      D    2       12
1      D    2       12

View 3 Replies View Related

SQL & PL/SQL :: To Remove Duplicate Rows From Output

Jun 14, 2011

I have a view and in that view i need to remove duplicate rows from output. For that i need to run select query in where clause of view if select query return true then we need to execute second condition.

my requirement in view like

And..........
And ((select count(*) from table A where conditions)=1 )then name is null
AND

in that code first we need to check first select query condition then we need to apply name is null condition. but i tried to run it but select query not run properly. because tables is used in View.

View 4 Replies View Related

PL/SQL :: Update Duplicate Records In Table With Sequence Value

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

View - Join Displays Duplicate Rows?

Apr 16, 2012

The view below creates, however displays duplicate rows. Why is this may I ask?

CREATE OR REPLACE VIEW customer_order_vw
AS
SELECT
a.customer_id,

[Code]....

View 1 Replies View Related

Duplicate Sequence Number For Identical Rows

Jul 5, 2010

I have a table:

Name
_____
Smith Street
Smith Street
John Street
Ed Street
Ed Street
Ed Street

and need to assign sequence numbers only when the record (Name) changes, e.g. :

Name Seq
_____ ____
Smith Street 1
Smith Street 1
John Street 2
Ed Street 3
Ed Street 3
Ed Street 3

I have experimented with row_number partition but then i just get the sequence returning to 1 when the name value changes.

If I grouped the records by Name I would like to have unique, sequential numbers: 1, 2, 3 but where there is the same name I would like the sequence to stop and the number to replicate?

View 9 Replies View Related

SQL & PL/SQL :: Delete The Duplicate Rows In A Table Without Using ROWID?

Mar 1, 2010

I want to delete the duplicate rows in a table without using ROWID.

I have the following data.

SNO SNAME SECTION
1 RAM A
2 SHYAM B
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F
5 RAJESH F
The Output Should be like this.

SNO SNAME SECTION
1 RAM A
2 SHYAM B
3 KISHOR C
3 KISHOR D
4 RAMESH E
5 RAJESH F

View 8 Replies View Related

SQL & PL/SQL :: How To Avoid Duplicate Rows From Being Inserted In The Table

Dec 21, 2009

I have one table in which I want to restrict some records from being inserted. I don't want to put any checked constraints. e.g. consider following table

transaction(
id number primary key,
txn_date timestamp(7),
payee varchar2(40),
amount number,
memo varchar2(40),
ref_num number
)

I want to write SQL which should not inset duplicate record.

e.g.

I have written one as bellow:

insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
(select count(*) from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12)=0;

Can I use exists/not exists, which query will be more appropriate. (Please consider that fields which I am using to filter out the duplicate transactions does not contain primary key.)

Can I write such SQL. Or do i check for duplicate rows one by one and then filter the duplicate records.

View 21 Replies View Related

SQL & PL/SQL :: Removing Duplicate Rows When Condition Is Matched

Mar 17, 2010

My requirement if id, join_date, join_time, result of table1 is matched with table2 at least one time then if repeating rows associated with the id should not come.Here is the test case.

create table table1
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
create table table2
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));

insert into table1 values (01, '20010101', '0500', 'PASS');
insert into table1 values (01, '20010102', '0501', 'FAIL');
insert into table1 values (02, '20010103', '0502', 'PASS');
insert into table1 values (03, '20010104', '0503', 'FAIL');
insert into table1 values (04, '20010105', '0504', 'PASS');
insert into table1 values (05, '20010106', '0505', 'FAIL');
[code]...

I have tried the below mentioned query, whether any better query is there than this because in real-time data have 2 millions of record in table 1 and 60 thousand in table2.

select distinct a.id, a.join_date, a.join_time, a.status
from table1 a, table2 b
where a.id = b.id
and (a.id, a.join_date, a.join_time, a.status) not in (select b.id, b.join_date, b.join_time, b.status
from table2 b)
and a.id = (
select distinct a.id
[code]....

View 20 Replies View Related

Application Express :: What Would Cause Oracle To Insert Duplicate Rows Into A Table

May 2, 2013

What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.

View 9 Replies View Related

SQL & PL/SQL :: Update All Rows At Once

Aug 28, 2011

I want to update salary column of emp table in a way that every value of salary column be increased by 1000, is this possible I can do this one statement only??

(Just FYI-

SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------

EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

)

[Code].....

View 2 Replies View Related

SQL & PL/SQL :: Update Query For 2 Lac Rows

Jun 12, 2013

I have 2 tables with 3 common columns (Col1, col2,Primary). One common column name is primary (oracle key word).Around 2 lakhs rows to be updated and No indexes are used on these tables. I need to write an update query as shown below.

Emp table

Col1col2primay
100101y
103104n
201105y
100101y

Dept table

Col1col2primay
100101null
103104null
000656null

Update query Result

Col1col2primary
100101y
103104n

View 16 Replies View Related

PL/SQL :: Update A Lot Of Rows With A Sequence?

Sep 30, 2013

, im having this trouble to update some records, the problem is i have  I have this table with some transactions and each row have a cd_cli (client code), dt_ven (date of the transaction) and a product id (cd_prod) the trouble is, i insert a row for each product in this table, then i have like this

 110001YURI AGUIRREWALLMA400V44-B469,0019/9/2013210001YURI AGUIRRESU-7N-B78,0019/9/2013310001YURI AGUIRREWALLSF35V22-S78,5719/9/2013410003WILLIANWALLSA470V22-BS449,002/9/2013 but i need to update the CD_TRANS for each row with the same number like all the YURI AGUIRRE recode must have like CD_TRANS 1, and the WILLIANs CD_TRANS must be 2 and the next must be cd_trans 3, but I've tried to update the table for a lot of ways but i don't have success trying to do this, the last thing i had tried was  

MERGE INTO TVEN_TEMP a
     USING (SELECT CD_CLI, DENSE_RANK () OVER (ORDER BY DT_VEN) new_seq_num
              FROM TVEN_TEMP
             WHERE CD_TRANS IS NULL) b
        ON (a.CD_CLI = b.CD_CLI)

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Program To Update 1000 Rows

Sep 16, 2010

May I have a PL/SQL program that can update 1000 rows.

View 4 Replies View Related

Subquery Returning Multiple Rows And Update

Mar 3, 2010

This is a surprisingly common one I've found on the web...even on devshed forum

I am updating one table from another (Updating Table A from Table B):

Table A
ID, Value
-- -----
1 A
1 A
2 B

Table B
ID, Value
-- -----
1 Animal
2 Box

Table A (modified)
ID, Value, Name
1 A Animal
1 A Animal
2 B Box

No I need to update a new column in Table A with the value in Table B.Value where the ID's from both tables match. Problem is: When I do this I get multiple rows and hence Oracle won't let me update this column. Now, I keep reading that for these types of updates, there has to be a one-to-one relationship...

Is this true...is there anyway of telling Oracle to update wherever it finds that ID, regardless of how many duplicate ID's there are?

This is quite a frustrating problem and most of the sites that I've looked for solutions try get the query one-to-one...problem is...with my table sets it's impossible to do that - I need to update wherever the id's match (even if it return multiple rows).

View 10 Replies View Related

Forms :: Update Random Rows In Table?

May 24, 2010

I have this requirement in Oracle FORMS ver 6i where I populate some records in a table in a datablock and display them on the screen. A facility needs to be provided to the end users to select the records randomly by just entering the count of the records in a non-d/b text field. The number entered in the count field will decide how many records are to be selected randomly.

I tried using the query -

UPDATE <table a>
SET <col 1> = <value 1>
WHERE rowid IN (SELECT rowid FROM (SELECT * FROM <table a> ORDER BY DBMS_RANDOM.VALUE)
WHERE <col 2> = <value 2>)
/

which I found on the link [URL] but while compiling, the Form gives a compilation error for the ORDER by clause - i'm sure there's no syntax error in the way I have written it...

View 5 Replies View Related

SQL & PL/SQL :: How To Find Out Which Rows Affected By Update Command

Dec 7, 2010

Assume that we have table Test with a column TestId and Status. This table is having 10,000 records. Now, we need to write a stored procedure that does the following:

1) Select top 10 rows whose "Status = NEW'

2) Update the selected top 10 rows with "Status = PROCESSING"

3) Return those top 10 rows.

Assuming there are hundreds of such requests coming to Oracle database, how can we make sure each request NEVER gets a duplicate record at any given point of time and how to fetch the records that have been updated individual request?

View 1 Replies View Related







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