Programming

awk '$0 in a{$2=0}{a[$0]=1}1' file

1) Unload the table to a file
2) Apply awk and modify the file
3) Truncate the table
4) Load the file back into the table

CREATE TABLE temp_table_1 AS (SELECT DISTINCT(*) FROM original_table) --Create a temp table with distinct entries
CREATE TABLE temp_table_2 AS (SELECT * FROM original_table) -- Create a temp table with all rows of the table
DELETE TOP 1 FROM temp_table_2 WHERE id IN (SELECT id FROM temp_table_1) --Delete 1 row for each id column
UPDATE temp_table_2 SET amount=0 WHERE ID > 0 --Update amount = 0 in all columns
DROP original_table -- Drop the table
CREATE TABLE original_table AS (SELECT * FROM temp_table_1,temp_table_2 WHERE TEMP_TABLE_1.id = temp_table_2.id) --Create the table joining both temp tables

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
      );

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

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: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