How to use a case statement when dependent on another table-CodePudding

I have two tables: TABLE_1 contains all current data, while TABLE_2 is my SOURCE table.

In TABLE_1, I have column called: STATUS. I would like to have a case statement, or some sort of function that allows me to update the status column in TABLE_1 if certain conditions are met.

CONDITION:

(A) IF status is currently INACTIVE, always remain INACTIVE. 
(B) IF status is currently ACTIVE AND PERSON_ID IS found in the SOURCE table, then update the status to: INACTIVE. 
(C) IF status is currently ACTIVE and  PERSON_ID IS NOT found in the SOURCE table, then update the status to: ACTIVE.

This is CURRENT TABLE_1:

This is the SOURCE TABLE_2:

This are the results of the status based on the conditions needed:

------------------------------------- CURRENT TABLE
DROP TABLE TABLE_1;
CREATE TABLE TABLE_1 (
PERSON_ID VARCHAR(100) NOT NULL,
COUNTRY_CODE VARCHAR(100) NOT NULL,
PURCHASE_DESCRIPTION VARCHAR(100) NOT NULL,
CLICKED_ID VARCHAR(100) NOT NULL,
CLAIMED_ID VARCHAR(100) NOT NULL,
BOUGHT_ID VARCHAR(100) NOT NULL,
STATUS VARCHAR(100) NOT NULL,
START_DATETIME DATE NOT NULL
);

INSERT INTO TABLE_1
VALUES (121144, 'USA', 'PURCHASE1131', '12','11','10','INACTIVE','2020-02-01'),
(121144, 'USA', 'PURCHASE1131', '20','10','10','ACTIVE','2021-04-01'),
(001111, 'CAN', 'PURCHASE2222', '40','20','10','ACTIVE','2020-02-01'),
(001222, 'AUS', 'PURCHASE1200', '11','15','15','ACTIVE','2020-02-01')

------------------------------------- SOURCE
DROP TABLE TABLE_2;
CREATE TABLE TABLE_2 (
PERSON_ID VARCHAR(100) NOT NULL
);

INSERT INTO TABLE_2 
VALUES (121144),
(001222)

Not sure if a CASE statement is the best to approach this:

select PERSON_ID,
COUNTRY_CODE,
PURCHASE_DESCRIPTION,
CLICKED_ID,
CLAIMED_ID,
BOUGHT_ID,
*** CASE WHEN STATUS IS INACTIVE ==> INACTIVEE (even if it is OR it is not found in source),
WHEN STATUS IS ACTIVE AND FOUND IN SOURCE ==> INACTIVE,
WHEN STATUS IS ACTIVE AND NOT FOUND IN SOURCEE ==> ACTIVE
START_DATETIME
from TABLE_1

CodePudding user response：

Using LEFT JOIN to get corresponding PERSON_ID value from the second table:

SELECT 
t1.PERSON_ID,
t1.COUNTRY_CODE,
t1.PURCHASE_DESCRIPTION,
t1.CLICKED_ID,
t1.CLAIMED_ID,
t1.BOUGHT_ID,
t1.START_DATETIME,
CASE WHEN t1.STATUS = 'INACTIVE' THEN 'INACTIVE'
     WHEN t1.STATUS = 'ACTIVE' AND t2.PERSON_ID IS NOT NULL  THEN 'INACTIVE'
     WHEN t1.STATUS = 'ACTIVE' AND t2.PERSON_ID IS NULL      THEN 'ACTIVE'
END AS STATUS
FROM TABLE_1 AS t1
LEFT JOIN TABLE_2 AS t2
  ON t1.PERSON_ID = t2.PERSON_ID;

Output: