Oracle SQL compare values from 2 tables and set yes/no-CodePudding

These are my example tables:

Table 1	table 2 column1	Table2 column2
CONCAT1	Concat2	Valid
A12	A11
A12	A12
B12
A12

i would like to add a Yes or No in the Valid column in table 2 , if A11 and A12 from concat 2 table 2 cand be found in Concat1 table1.

It's probably simple, but i can't figure it out at all.

Expected:

Table 1	table 2 column1	Table2 column2
CONCAT1	Concat2	Valid
A12	A11	No
A12	A12	Yes
B12
A12

Thanks !!!

CodePudding user response：

Use EXISTS and a correlated sub-query:

SELECT concat2,
       CASE
       WHEN EXISTS (SELECT 1 FROM table1 t1 WHERE t1.concat1 = t2.concat2)
       THEN 'Yes'
       ELSE 'No'
       END AS valid
FROM   table2 t2

Which, for the sample data:

CREATE TABLE Table1 (CONCAT1) AS
SELECT 'A12' FROM DUAL UNION ALL
SELECT 'A12' FROM DUAL UNION ALL
SELECT 'B12' FROM DUAL UNION ALL
SELECT 'A12' FROM DUAL;

CREATE TABLE table2 (concat2, valid) AS
SELECT 'A11', CAST(NULL AS VARCHAR2(3)) FROM DUAL UNION ALL
SELECT 'A12', NULL FROM DUAL

Outputs:

CONCAT2 VALID

A11 No

A12 Yes

CONCAT2	VALID
A11	No
A12	Yes

Or, if you want to update the table then:

UPDATE table2 t2
SET valid = CASE
            WHEN EXISTS (SELECT 1 FROM table1 t1 WHERE t1.concat1 = t2.concat2)
            THEN 'Yes'
            ELSE 'No'
            END
WHERE valid IS NULL;

db<>fiddle here