maephisto - 1 year ago 68
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!

At speed you can do this:

insert into users
select