Update a column in table A based on values in table B using a CASE statement

I got a tricky question.

I would have a table A that is composed of two columns A.ID and A.Cat1_Avail. The Cat1_Avail-column is empty as of now.

I would like to update the values in A.Cat1Avail based on a logical question. In particular, I have another table B that is composed of B.ID and B.Feature. B.Feature can hold the following values: Cat1, Cat2 or NULL.

For matching ID’s, I would like to insert a 1 in A.Cat1_Avail given the matching record in table B holds the value Cat1 in B.Feature. In case of Cat2, A.Cat1_Avail should be 0.

In summary, I would like to update a column based on logical questions (do the ID’s match? what is the content of B.Feature?).

I hope this makes things clear and is not too specific.

I guess this problem could be solved with several commands in sequence. But I would like to have a single command that can handle this problem.

Answer

Use merge to achieve it as following:

MERGE INTO TABLEA A USING (
                              SELECT
                                  ID,
                                  CASE FEATURE
                                      WHEN 'cat1'   THEN 1
                                      WHEN 'cat2'   THEN 0
                                  END AS CAT
                              FROM
                                  TABLEB
                          ) B ON ( A.ID = B.ID )
WHEN MATCHED THEN 
UPDATE SET A.CAT1_AVAIL = B.CAT

Cheers!!