maephisto maephisto - 6 months ago 15
SQL Question

Oracle merge operation

Let's assume the next two tables:

users (username, region, location, department, subdepartment)


and

structure (region, location, department, subdepartment)


At start, in the users table, each username appears in one record. I would like that if a username has the subdepartment column empty, to be automatically distributed to all subdepartement defined for that department in the structure table.

Meaning, instead of just one record in the users table, at the end there will be N records, where N represents the number of subdepartments defined in the structure table for the original region,location,department combination.

I've tried doing this with MERGE statement, but i can't figure it out. How could I do this ?
Thank you!

Answer

At speed you can do this:

insert into users
select 
   u.username, u.region, u.location, u.department, s.subdepartment
from users u join structure s 
   on (u.region=s.region and u.location=s.location and u.department = s.department)
where u.subdepartment is null;

delete from users where subdepartment is null;

but, for above, you must be sure that every department has subdepartments.(If it is not the case, you must do some simple PL/SQL)

Comments