Updating a field based on values in the other two scopes
I have a database table containing RACE, ETHNICITY and ETH fields. I need to evaluate the RACE and ETHNICITY fields and fill in ETH using a number of cases: if Race = W and Ethncity = 1, then ETH = Caucasian, etc.
Can anyone advise me on the best way to structure a stored procedure for this?
+2
source to share
2 answers
I would 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 of your case statement. On some lines, you can do two conditions (like on the first two lines), and on others, you can do it (like on the third line).
+9
source to share
Normalize your data, create a new table with a composite primary key in Race + Ethnicity:
YourNewTable
Race CHAR(1) PK
Ethnicity CHAR(1) PK
ETH VARCHAR(50)
put the foreign key into 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
+1
source to share