How do I insert the missing rows

rball_50711 0
Viewed 50

I currently have a table like this:

FK_ID Name Updated_Date
1 PLANNED [Some Date]
1 DOING [Some Date]
2 PLANNED [Some Date]
2 DOING [Some Date]
2 DONE [Some Date]
3 PLANNED [Some Date]

The problem I'm having is I'd like to insert all missing PLANNED, DOING, DONE for every set of FK_ID's into the table so the result would be

FK_ID Name Updated_Date
1 PLANNED [Some Date]
1 DOING [Some Date]
1 DONE [Blank]
2 PLANNED [Some Date]
2 DOING [Some Date]
2 DONE [Some Date]
3 PLANNED [Some Date]
3 DOING [Blank]
3 DONE [Blank]

I had the idea to create a table, although I'm sure a temp table would be fine that would just be

Name
PLANNED
DOING
DONE

and then do something with OUTER LEFT JOIN and/or NOT IN's but I'm not getting what I'm looking for. I'm thinking I need to have a query with a subquery with those but SQL guy I am not. Here's what I have so far:

SELECT e.*
FROM ActualData e
OUTER LEFT JOIN TempTable i
ON e.Name = i.Name WHERE i.Name IS NULL

This does give me some data, but only the ones that don't appear in ANY of the records of the ActualData table.

oracle sql
2 Answers

One approach could be to use a cross join to get all the combinations, and then remove those that already exist:

SELECT fk_id, t.name
FROM   ActualData
CROSS JOIN temp_table t
MINUS
SELECT fk_id, name
FROM   ActualData

Of course, this query can be used to insert the "missing" rows back into ActualData:

INSERT INTO ActualData (fk_id, name)
SELECT fk_id, t.name
FROM   ActualData
CROSS JOIN temp_table t
MINUS
SELECT fk_id, name
FROM   ActualData
user_24227760

You may use partitioned join with static list on status column to generate rows with missing statuses for each partition column (fk_id).

with test_tab(FK_ID, Name, Updated_Date) as (
select 1, 'PLANNED', '[Some Date]' from dual union all
select 1, 'DOING', '[Some Date]' from dual union all
select 2, 'PLANNED', '[Some Date]' from dual union all
select 2, 'DOING', '[Some Date]' from dual union all
select 2, 'DONE', '[Some Date]' from dual union all
select 3, 'PLANNED', '[Some Date]' from dual
)
select
  a.fk_id,
  b.column_value as name,
  a.updated_date
from table(sys.odcivarchar2list(
    'PLANNED',
    'DOING',
    'DONE'
  )) b
  left join test_tab a
    partition by (fk_id)
  on a.name = b.column_value

FK_ID | NAME    | UPDATED_DATE
----: | :------ | :-----------
    1 | DOING   | [Some Date] 
    1 | DONE    | null        
    1 | PLANNED | [Some Date] 
    2 | DOING   | [Some Date] 
    2 | DONE    | [Some Date] 
    2 | PLANNED | [Some Date] 
    3 | DOING   | null        
    3 | DONE    | null        
    3 | PLANNED | [Some Date]

db<>fiddle here

user_27787100
Related