CODEWITHSUNDEEP
databaseoracle-databaseoracle11goracle10g
Jeremy
Jeremy

Reputation: 13

Replace Values in Oracle DB with set of test data

I am currently working on a project where I have a copy of a production Oracle database with some production values that I want to replace with a set of test values (with the assumption that I have more production records than test ones and I will have duplicates).

Here is a sample of what I am looking to do:

Data Set Sample

Any suggestions would be greatly appreciated.

Upvotes: 0

Views: 70

Answers (1)

Thomas Tschernich
Thomas Tschernich

Reputation: 1282

One approach would be to number the rows by their rowid, then use a modulo operation to match the original data to your test data table, like this:

merge into customer c
using (
    with cte_customer as (
        select rowid xrowid, mod(row_number() over (order by rowid)-1,(select count(*) from test_data))+1 rownumber
        from customer
        order by rowid
    ), cte_testdata as (
        select row_number() over (order by rowid) rownumber, first_name, last_name, email
        from test_data
        order by rowid   
    )            
    select c.xrowid, t.last_name, t.first_name, t.email
    from cte_customer c
    left outer join cte_testdata t on (t.rownumber = c.rownumber)
    order by c.xrowid
) u
on (c.rowid = u.xrowid)
when matched then update set
    c.last_name = u.last_name,
    c.first_name = u.first_name,
    c.email = u.email

Upvotes: 2

Related Questions