IF COL_LENGTH('dbo.asr_loc_mast', 'area_id') IS NULL
BEGIN
    ALTER TABLE [dbo].[asr_loc_mast]
        ADD [area_id] INT NULL;
END;

EXEC sys.sp_executesql N'
UPDATE [dbo].[asr_loc_mast]
SET [area_id] =
    CASE
        WHEN [crn_no] BETWEEN 1 AND 6 THEN 1
        WHEN [crn_no] BETWEEN 7 AND 18 THEN 2
        WHEN [crn_no] BETWEEN 19 AND 28 THEN 3
        ELSE [area_id]
    END
WHERE [area_id] IS NULL
  AND [crn_no] BETWEEN 1 AND 28;
';

IF COL_LENGTH('dbo.asr_wrk_mast', 'area_id') IS NULL
BEGIN
    ALTER TABLE [dbo].[asr_wrk_mast]
        ADD [area_id] INT NULL;
END;

EXEC sys.sp_executesql N'
UPDATE m
SET area_id = CASE
        WHEN m.io_type >= 100 THEN COALESCE(source_loc.area_id, target_loc.area_id)
        ELSE COALESCE(target_loc.area_id, source_loc.area_id)
    END
FROM dbo.asr_wrk_mast m
LEFT JOIN dbo.asr_loc_mast target_loc ON target_loc.loc_no = m.loc_no
LEFT JOIN dbo.asr_loc_mast source_loc ON source_loc.loc_no = m.source_loc_no
WHERE m.area_id IS NULL
  AND CASE
        WHEN m.io_type >= 100 THEN COALESCE(source_loc.area_id, target_loc.area_id)
        ELSE COALESCE(target_loc.area_id, source_loc.area_id)
      END IS NOT NULL;
';

IF OBJECT_ID('dbo.asr_wrk_mast_log', 'U') IS NOT NULL
BEGIN
    IF COL_LENGTH('dbo.asr_wrk_mast_log', 'area_id') IS NULL
    BEGIN
        ALTER TABLE [dbo].[asr_wrk_mast_log]
            ADD [area_id] INT NULL;
    END;

    EXEC sys.sp_executesql N'
    UPDATE m
    SET area_id = CASE
            WHEN m.io_type >= 100 THEN COALESCE(source_loc.area_id, target_loc.area_id)
            ELSE COALESCE(target_loc.area_id, source_loc.area_id)
        END
    FROM dbo.asr_wrk_mast_log m
    LEFT JOIN dbo.asr_loc_mast target_loc ON target_loc.loc_no = m.loc_no
    LEFT JOIN dbo.asr_loc_mast source_loc ON source_loc.loc_no = m.source_loc_no
    WHERE m.area_id IS NULL
      AND CASE
            WHEN m.io_type >= 100 THEN COALESCE(source_loc.area_id, target_loc.area_id)
            ELSE COALESCE(target_loc.area_id, source_loc.area_id)
          END IS NOT NULL;
    ';
END;