Update Statement in Oracle Using SQL or Pl/SQL to Update First Duplicate Row Only

How to update single row of multiple duplicate rows in Oracle SQL

If you need all four rows, just remove DISTINCT, because it forces unique record set:

select group_id, invoice_amt, insert_date from table

If you need to change date on all duplicates but one value and have no unique identifier within a table itself you can use internal Oracle pseudocolumn ROWID:

update table t
set insert_date = < your date here >
where rowid < (
  select max(rowid)
  from table t2
  where t.group_id = t2.group_id
    and t.invoice_amt = t2.invoice_amt
  );

Update Query For Duplicate Records Oracle

You may use the count analytic function with LPAD

SELECT card_no
    ,LPAD('D', count(civil_no) OVER (
            PARTITION BY civil_no ORDER BY card_no
            ), 'D') || civil_no as output
FROM t;

Demo

It's not clear which table you want to update, you may do so with a correlated update using the above select or a MERGE INTO

UPDATE t t1 
SET    output = (SELECT output 
                 FROM   (SELECT card_no, 
                                lpad('D', COUNT(civil_no) 
                                            over ( 
                                              PARTITION BY civil_no 
                                              ORDER BY card_no ), 'D') 
                                || civil_no AS output 
                         FROM   t) t2 
                 WHERE  t1.card_no = t2.card_no); 

Oracle SQL - Update Duplicate with a max value

You can use case clause to update value with TRUE or FALSE.

update TABLE1
set tru_false = case when (TIME, duration) in (select time, max(duration) from TABLE1 group by time)
                then 'TRUE'
                else 'FALSE'
                END

Here is a small demo that I hope it will help

Update a column of same table if there is duplicate records

You can use the analytical function row_number() and rowid to get the rows:

UPDATE test_dup
   SET done = 'error'
 WHERE ROWID IN (SELECT ROWID
                   FROM (SELECT acc_num, tel_num, imsi, ROW_NUMBER () OVER (PARTITION BY acc_num, tel_num, imsi ORDER BY acc_num) AS ROW_NUMBER FROM test_dup)
                  WHERE ROW_NUMBER > 1)

Make sure only one row is updated in PL/SQL?

UPDATE your_table SET Column = 'One' WHERE Key = 'Example';
IF SQL%ROWCOUNT <> 1 THEN
  ROLLBACK;
  RAISE_APPLICATION_ERROR( -20000, 'Incorrect number of rows updated for Key "Example".' )
END IF;

Or:

DECLARE
  p_rowid ROWID;
  p_key   YOUR_TABLE.KEY%TYPE := 'Example';
BEGIN
  SELECT ROWID INTO p_rowid FROM your_table WHERE Key = p_key;

  UPDATE your_table SET Column = 'One' WHERE ROWID = p_rowid;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR( -20000, 'No data found for key "'|| p_key || '".' );
  WHEN TOO_MANY_ROWS THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR( -20001, 'More than one row found for key "'|| p_key || '".' );
END;

Oracle - update only one row without using rowid

ROWID is a unique identifier of each row in the database.

ROWNUM is a unique identifier for each row in a result set.

You should be using the ROWNUM version, but you will need an ORDER BY to enforce a sorting order, otherwise you won't have any guarantees what is the "first" row returned by your query and you might be updating another row.

update addrView
set home='current'
where (tl, tr) = (
   select tl, tr 
   from (select tl, tr
         from addrView 
         where (tl = '7' and tr = '2')
         order by col_1
             , col_2
             , col_3 etc.
        ) result_set
    where rownum = 1);

But, if you don't care about what data is in the first row returned by your query, then you can use only rownum = 1.

update addrView
set home = 'current'
where (tl = '7' and tr = '2')
    and rownum = 1;

How to update record which is duplicated PL/SQL in procedure

You can do it with one command:

merge into sales_staff_08 d
using (select firstname ||surname || row_number() 
           over (partition by username order by firstname) un, rowid 
       from sales_staff_08 ) s
on (d.rowid = s.rowid)
when matched then update set d.username = s.un;

Here is a sqlfiddle demo

As @Plouf stated this can also be done as an update command:

update sales_staff_08 d
set d.username = (
  select un from (
    select firstname ||surname || row_number() 
            over (partition by firstname, surname order by firstname) un 
     from sales_staff_08
  ) s
  where s.rowid = d.rowid
)

Here is another sqlfiddle

---

Related Topics