Sponsored Content
Full Discussion: [Solved] duplicate records
Top Forums Programming [Solved] duplicate records Post 302582492 by radoulov on Friday 16th of December 2011 06:54:00 AM
Old 12-16-2011
Something like this:

Code:
update <table_name>
set amount = 0
where rowid != (
    select max(rowid)
    from <table_name> a
    where t.id = a.id
    and t.amount = a.amount
      );

For example:

Code:
12:51:33 SQL> select * from t order by 1, 2 desc;

        ID     AMOUNT
---------- ----------
         1        100
         1        100
         1        100
         1        100
         2        200
         2        200

6 rows selected.

Elapsed: 00:00:00.01
12:51:51 SQL> update t
set amount = 0
where rowid != (
    select max(rowid)
        from t a
        where t.id = a.id
        and t.amount = a.amount
          );12:51:57   2  12:51:57   3  12:51:57   4  12:51:57   5  12:51:57   6  12:51:57   7  12:51:57   8

4 rows updated.

Elapsed: 00:00:00.00
12:51:58 SQL> select * from t order by 1, 2 desc;

        ID     AMOUNT
---------- ----------
         1        100
         1          0
         1          0
         1          0
         2        200
         2          0

6 rows selected.

Elapsed: 00:00:00.00

And another one:

Code:
update t
set amount = 0
where rowid in ( 
  select rid
  from ( 
    select rowid rid, row_number() over 
      (partition by id order by rowid ) rn
    from t 
      )
  where rn > 1 );

Example:


Code:
13:00:26 SQL> select * from t order by 1, 2 desc;

        ID     AMOUNT
---------- ----------
         1        100
         1        100
         1        100
         1        100
         2        200
         2        200

6 rows selected.

Elapsed: 00:00:00.00
13:00:34 SQL> update t
set amount = 0
where rowid in (
  select rid
  from (
    select rowid rid, row_number() over
          (partition by id order by rowid ) rn
    from t
          )
  where rn > 1 );13:00:43   2  13:00:43   3  13:00:43   4  13:00:43   5  13:00:43   6  13:00:43   7  13:00:43   8  13:00:43   9  13:00:43  10

4 rows updated.

Elapsed: 00:00:00.01
13:00:43 SQL> select * from t order by 1, 2 desc;

        ID     AMOUNT
---------- ----------
         1        100
         1          0
         1          0
         1          0
         2        200
         2          0

6 rows selected.

Elapsed: 00:00:00.00


Last edited by radoulov; 12-16-2011 at 08:01 AM..
This User Gave Thanks to radoulov For This Post:
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Records Duplicate

Hi Everyone, I have a flat file of 1000 unique records like following : For eg Andy,Flower,201-987-0000,12/23/01 Andrew,Smith,101-387-3400,11/12/01 Ani,Ross,401-757-8640,10/4/01 Rich,Finny,245-308-0000,2/27/06 Craig,Ford,842-094-8740,1/3/04 . . . . . . Now I want to duplicate... (9 Replies)
Discussion started by: ganesh123
9 Replies

2. Shell Programming and Scripting

combine duplicate records

I have a .DAT file like below 23666483030000653-B94030001OLFXXX000000120081227 23797049900000654-E71060001OLFXXX000000220081227 23699281320000655 E71060002OLFXXX000000320081227 22885068900000652 B86860003OLFXXX592123320081227 22885068900000652 B86860003ODL-SP592123420081227... (8 Replies)
Discussion started by: kshuser
8 Replies

3. Shell Programming and Scripting

Remove duplicate records

I want to remove the records based on duplicate. I want to remove if two or more records exists with combination fields. Those records should not come once also file abc.txt ABC;123;XYB;HELLO; ABC;123;HKL;HELLO; CDE;123;LLKJ;HELLO; ABC;123;LSDK;HELLO; CDF;344;SLK;TEST key fields are... (7 Replies)
Discussion started by: svenkatareddy
7 Replies

4. Shell Programming and Scripting

Remove Duplicate Records

Hi frinds, Need your help. item , color ,desc ==== ======= ==== 1,red ,abc 1,red , a b c 2,blue,x 3,black,y 4,brown,xv 4,brown,x v 4,brown, x v I have to elemnet the duplicate rows on the basis of item. the final out put will be 1,red ,abc (6 Replies)
Discussion started by: imipsita.rath
6 Replies

5. UNIX for Dummies Questions & Answers

Getting non-duplicate records

Hi, I have a file with these records abc xyz xyz pqr uvw cde cde In my o/p file , I want all the non duplicate rows to be shown. o/p abc pqr uvw Any suggestions how to do this? Thanks for the help. rs (2 Replies)
Discussion started by: rs123
2 Replies

6. UNIX for Dummies Questions & Answers

Need to keep duplicate records

Consider my input is 10 10 20 then, uniq -u will give 20 and uniq -dwill return 10. But i need the output as , 10 10 How we can achieve this? Thanks (4 Replies)
Discussion started by: pandeesh
4 Replies

7. Shell Programming and Scripting

Deleting duplicate records from file 1 if records from file 2 match

I have 2 files "File 1" is delimited by ";" and "File 2" is delimited by "|". File 1 below (3 record shown): Doc1;03/01/2012;New York;6 Main Street;Mr. Smith 1;Mr. Jones Doc2;03/01/2012;Syracuse;876 Broadway;John Davis;Barbara Lull Doc3;03/01/2012;Buffalo;779 Old Windy Road;Charles... (2 Replies)
Discussion started by: vestport
2 Replies

8. Shell Programming and Scripting

How to keep the last 2 records from duplicate entries?

Gents, Please how I can get only the last 2 records from repetead values, from column 2 input 1 1011 1 1011 1 1012 1 1012 1 5001 1 5001 1 5002 1 5002 1 5003 1 5003 1 7001 1 7001 1 7002 1 7002 (2 Replies)
Discussion started by: jiam912
2 Replies

9. Shell Programming and Scripting

Duplicate records

Gents, I have a file which contends duplicate records in column 1, but the values in column 2 are different. 3099753489 3 3099753489 5 3101954341 12 3101954341 14 3102153285 3 3102153285 5 3102153297 3 3102153297 5 I will like to get something like this: output desired... (16 Replies)
Discussion started by: jiam912
16 Replies

10. Shell Programming and Scripting

Duplicate records

Gents, Please give a help file --BAD STATUS NOT RESHOOTED-- *** VP 41255/51341 in sw 2973 *** VP 41679/51521 in sw 2973 *** VP 41687/51653 in sw 2973 *** VP 41719/51629 in sw 2976 --BAD COG NOT RESHOOTED-- *** VP 41689/51497 in sw 2974 *** VP 41699/51677 in sw 2974 *** VP... (18 Replies)
Discussion started by: jiam912
18 Replies
All times are GMT -4. The time now is 07:27 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy