1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
merge into t_data d
using (
with n_data as (
select d.ROWID as nsq, d.* from t_data d
),
x_male_names as (
select rownum as id, ln.last_name, fn.first_name, fn.gender
from a_lastnames ln, a_firstnames fn
where fn.gender = 'm'
),
x_female_names as (
select rownum as id, ln.last_name, fn.first_name, fn.gender
from a_lastnames ln, a_firstnames fn
where fn.gender = 'f'
),
male_random_ids as (
select nsq, round(dbms_random.value(1,(select count(id) from x_male_names))) as rid
from n_data where gender = 'm'
),
female_random_ids as (
select nsq, round(dbms_random.value(1,(select count(id) from x_female_names))) as rid
from n_data where gender = 'f'
)
select d.nsq, nvl(m.last_name, f.last_name) as last_name, nvl(m.first_name, f.first_name) as first_name, d.gender
from n_data d
left join male_random_ids mr on mr.nsq = d.nsq and d.gender = 'm'
left join x_male_names m on m.id = mr.rid
left join female_random_ids fr on fr.nsq = d.nsq and d.gender = 'f'
left join x_female_names f on f.id = fr.rid
)
nd on (nd.nsq = d.ROWID)
when matched then
update set d.last_name = nd.last_name, d.first_name = nd.first_name
; |
