Updating a Field based on Values in Two Other Fields

Question

I have a database table containing fields RACE, ETHNICITY and ETH. I need to evaluate RACE and ETHNICITY fields and populate ETH using a series of cases: if Race = W and Ethncity = 1 then ETH = Caucasian etc.

Can someone advise me on the best way to structure a stored procedure to accomplish this?

Answer 1

I'd do a CASE statement in your update:

UPDATE yourtable
   SET Eth = CASE
               WHEN Race = 'W' AND Ethnicity = 1 THEN 'Caucasian'
               WHEN Race = 'B' AND Ethnicity = 2 THEN 'African-American'
               WHEN Race = 'H' THEN 'Hispanic' --Ethnicity is irrelevant
               ...whatever other pairs of conditions you want...
             END
 FROM yourtable

In this case, you can have whatever conditions you want on each line in your case statement. In some lines, you can do two conditions (as in the first two lines), and in others you can do one (as in line three).

Answer 2

Normalize your data, create a new table, with a composite primary key on Race+Ethnicity:

YourNewTable

Race       CHAR(1)     PK
Ethnicity  CHAR(1)     PK
ETH        VARCHAR(50)

make a foreign key to your other table, and join to show ETH:

SELECT
    o.Race
       ,o.ETHNICITY  
       ,n.ETH
    FROM YourTable               o
        INNER JOIN YourNewTable  n ON o.Race=n.Race AND o.Ethnicity=n.Ethnicity