I have these two tables:

 -- Create the table
CREATE TABLE old_table (
    name1 VARCHAR(20),
    name2 VARCHAR(20),
    origin_date DATE,
    var1 VARCHAR(10),
    end_date DATE,
    status VARCHAR(10)
);

-- Insert the data
INSERT INTO old_table(name1, name2, origin_date, var1, end_date, status) VALUES
('red_1', 'red', '2010-01-01', 'aaa', NULL, 'active'),
('red_2', 'red', '2011-01-01', 'bbb', '2020-01-01', 'inactive'),
('blue_1', 'blue', '2005-01-01', 'ccc', '2020-01-01', 'inactive'),
('green_1', 'green', '2005-01-01', 'ddd', '2020-01-01', 'inactive'),
('purple_1', 'purple', '2001-01-01', 'fff', NULL, 'active'),
('pink_1', 'pink', '2002-01-01', 'ggg', NULL, 'active');

  
-- Create the table
CREATE TABLE new_table (
    name1 VARCHAR(20),
    name2 VARCHAR(20),
    origin_date DATE,
    var1 VARCHAR(10),
    today DATE
);

-- Insert the data
INSERT INTO new_table (name1, name2, origin_date, var1, today) VALUES
('red_1', 'red', '2010-01-01', 'aaa', '2020-01-03'),
('orange_1', 'orange', '2012-01-01', 'zzz', '2020-01-01');

When comparing the new_table to the old_table:

• pink and purple have no longer survived (end_date = new_table.today, status = inactive)
• red has still survived (end_date = NULL, status = active)
• orange has now appeared (end_date = NULL, status = active)

The final result should look like this:

| name1    | name2  | origin_date | var1 | end_date   | status  |
|----------|--------|-------------|------|------------|---------|
| red_1    | red    | 2010-01-01  | aaa  | NULL       | active  |
| red_2    | red    | 2011-01-01  | bbb  | 2020-01-01 | inactive|
| blue_1   | blue   | 2005-01-01  | ccc  | 2020-01-01 | inactive|
| green_1  | green  | 2005-01-01  | ddd  | 2020-01-01 | inactive|
| purple_1 | purple | 2001-01-01  | fff  | 2020-01-03 | inactive|
| pink_1   | pink   | 2002-01-01  | ggg  | 2020-01-03 | inactive|
| orange_1 | orange | 2012-01-01  | zzz  | NULL       | active  |

I tried writing SQL code to reflect this requirements:

SELECT 
    o.name1,
    o.name2,
    o.origin_date,
    o.var1,
    CASE 
        WHEN n.name1 IS NULL THEN o.end_date 
        ELSE NULL 
    END AS end_date,
    CASE 
        WHEN n.name1 IS NULL THEN 'inactive' 
        ELSE 'active' 
    END AS status
FROM 
    old_table o
LEFT JOIN 
    new_table n ON o.name1 = n.name1
UNION ALL
SELECT 
    n.name1,
    n.name2,
    n.origin_date,
    n.var1,
    CASE 
        WHEN o.name1 IS NULL THEN NULL 
        ELSE n.today 
    END AS end_date,
    'active' AS status
FROM 
    new_table n
LEFT JOIN 
    old_table o ON n.name1 = o.name1
WHERE 
    o.name1 IS NULL;

Problem : The end_dates for purple_1 and pink_1 are NULL when they should be non-null:

     name1  name2 origin_date var1   end_date   status
    red_1    red  2010-01-01  aaa       <NA>   active
    red_2    red  2011-01-01  bbb 2020-01-01 inactive
   blue_1   blue  2005-01-01  ccc 2020-01-01 inactive
  green_1  green  2005-01-01  ddd 2020-01-01 inactive
 purple_1 purple  2001-01-01  fff       NULL inactive
   pink_1   pink  2002-01-01  ggg       NULL inactive
 orange_1 orange  2012-01-01  zzz       <NA>   active

Can someone please show me how to correct this?