Tukukkk

I have a large table in which I store region/state/province codes (two letters) from different countries. I use these region codes further downstream for multiple process. One of the steps I do is a cleanup of regions to ensure it is a valid region/state/province code. I have a lookup table consisting of a country vs region code to validate each column.

6 columns are validated separately. Is there a way to do this better?

UPDATE  table_region

SET     a_region = NULL

WHERE   a_region IS NOT NULL

        AND country IS NOT NULL

        AND dbo.IsValidRegion(country,a_region) = 0;



UPDATE  table_region

SET     b_region = NULL

WHERE   b_region IS NOT NULL

        AND country IS NOT NULL

        AND dbo.IsValidRegion(country,b_region) = 0;



UPDATE  table_region

SET     c_region = NULL

WHERE   c_region IS NOT NULL

        AND country IS NOT NULL

        AND dbo.IsValidRegion(country, c_region) = 0;         



UPDATE  table_region

SET     d_region = NULL

WHERE   d_region IS NOT NULL

        AND country IS NOT NULL

        AND dbo.IsValidRegion(country,d_region) = 0;  



UPDATE  table_region

SET     e_region = NULL

WHERE   e_region IS NOT NULL

        AND country IS NOT NULL

        AND dbo.IsValidRegion(country, e_region) = 0;  



UPDATE  table_region

SET     f_region = NULL

WHERE   f_region IS NOT NULL

        AND country IS NOT NULL

        AND dbo.IsValidRegion(country, f_region) = 0;

The function is like this:

ALTER Function [dbo].[IsValidRegion](@country VARCHAR(20), @value 



VARCHAR(20))

Returns INT

AS

BEGIN

    DECLARE @res INT

    BEGIN

        IF EXISTS (SELECT 1 FROM mapping.region WHERE country = @country AND region_code = @value)

            SELECT @res = 1

        ELSE

            SELECT @res = 0

    END

    RETURN @res

END

Here's a one hit way of doing this:

    UPDATE t

    SET     

       a_region = ra.region_code,

       b_region = rb.region_code,

       c_region = rc.region_code,

       d_region = rd.region_code,

       e_region = re.region_code,

       f_region = rf.region_code

    FROM

       table_region t

       LEFT OUTER JOIN mapping.region ra WHERE t.country = ra.country AND t.a_region = ra.region_code

       LEFT OUTER JOIN mapping.region rb WHERE t.country = rb.country AND t.b_region = rb.region_code

       LEFT OUTER JOIN mapping.region rc WHERE t.country = rc.country AND t.c_region = rc.region_code

       LEFT OUTER JOIN mapping.region rd WHERE t.country = rd.country AND t.d_region = rd.region_code

       LEFT OUTER JOIN mapping.region re WHERE t.country = re.country AND t.e_region = re.region_code

       LEFT OUTER JOIN mapping.region rf WHERE t.country = rf.country AND t.f_region = rf.region_code

It basically works by left joining the region table 6 times, once for each of the *_region columns.

• If the relation works out, then the r*.region_code will be populated with a value (which means the *_region column is set to the same value it currently is, i.e. a non-op).


• If it doesn't work out, then the join fails, null is present in r*.region_code
  

After you run this as a one time fix, to identify the ones that are failed (maybe you should remove them to another table) you should consider making table_region(country,*_region) have multiple foreign keys to mapping.region(country,region_code) to ensure that records cannot be inserted into table_region that don't have a valid country/region mapping