Search code examples
sqloracle-databaserownum

Oracle SQL - Update a database field with the content of the next record

I am trying to anonymize the 'name' field of a customer table. I want to replace the 'name' of every record with the name from the customer in the next record. (I know: That's not really anonymous but 'name' and 'customerId' won't match after that. That's enough for my purposes)

I tried this, but I get an ORA-01747 error.

UPDATE Customer A
   SET NAME =
          (SELECT NAME
             FROM Customer 
            WHERE ROWNUM = A.ROWNUM + 1)

What is wrong? How can I update every 'name'-field with the content of the next 'name'-field in the table?

Solution

  • Mix'em all!!!

    merge into Customer dest
using (
   select r, name from
   (select name, row_number() over (order by dbms_random.value) n from Customer)
   join (select rowid r, rownum n from Customer) using(n) 
) src
on (dest.rowid = src.r)
when matched then update set
   dest.name = src.name;