FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE [data_log] (
    [id]        int identity(1,1),
    [dataID]    bigint,
    [field]     varchar(128),
    [sf_data]   varchar(500),
    [h_data]    varchar(500),
    [score]     float,
    [action]    varchar(128)
);

INSERT INTO [Data_log] VALUES 
 (3605013844, '[MailingCity]', 'Flat', 'Flatt', NULL, NULL),
    (3605013844, '[MailingState]', 'KY', 'Kentucky', NULL, NULL),
    (3605013844, '[MailingZIP]', '41301', '41301', NULL, NULL),
    (1874281127, '[MailingCity]', 'EDMONTON', 'Edmonton', NULL, NULL),
    (1874281127, '[MailingState]', 'AB', 'Alberta', NULL, NULL),
    (1874281127, '[MailingZIP]', 'T6M 2K1', 'T6M 2K1', NULL, NULL),
    (2077170855, '[MailingCity]', 'Van Buren Point', 'Van Buren Point', NULL, NULL),
    (2077170855, '[MailingState]', 'NY', 'New York', NULL, NULL),
    (2077170855, '[MailingZIP]', '14166', '14166', NULL, NULL),
    (1874281127, '[MailingState]', 'PA', 'Ontario', NULL, NULL),
    (1874281127, '[MailingState]', 'IL', 'Missouri', NULL, NULL)

CREATE TABLE [dbo].[CityStateInfo](
    [City] [varchar](255) NULL,
    [State_abbr] [varchar](10) NULL,
    [State_long] [varchar](50) NULL,
    [Zip] [varchar](20) NULL,
    [County] [varchar](50) NULL,
    [Country] [varchar](50) NULL,
    [Longitude] [varchar](15) NULL,
    [Latitude] [varchar](15) NULL,
    [StateFIPS] [varchar](10) NULL,
    [CountryFIPS] [varchar](10) NULL,
    [TimeZone] [int] NULL,
    [cleanCity] [varchar](255) NULL,
    [Country_abbr] [varchar](10) NULL,
    [foreignCity] [varchar](255) NULL,
    [foreignState] [varchar](255) NULL
)

INSERT INTO [CityStateInfo] VALUES
('AARON','KY','Kentucky','42602','RUSSELL','United States','-85.121708','36.751734','21','207','6','AARON','US',NULL,NULL),
('AARON','KY','Kentucky','42602','CLINTON','United States','-85.121708','36.751734','21','053','6','AARON','US',NULL,NULL),
('ADRIAN','MO','Missouri','64720','BATES','United States','-94.398772','38.433513','29','013','6','ADRIAN','US',NULL,NULL),
('ADVANCE','MO','Missouri','63730','CAPE GIRARDEAU','United States','-89.911055','37.058424','29','031','6','ADVANCE','US',NULL,NULL),
('SHIRLEY','NY','New York','11967','SUFFOLK','United States','-72.880184','40.794219','36','103','5','SHIRLEY','US',NULL,NULL),
('SHOKAN','NY','New York','12481','ULSTER','United States','-74.214799','41.982148','36','111','5','SHOKAN','US',NULL,NULL),
('KANATA','ON','Ontario','K2M 0A8',NULL,'Canada',NULL,NULL,NULL,NULL,'5','KANATA','CA',NULL,NULL),
('KANATA','ON','Ontario','K2M 0A9',NULL,'Canada',NULL,NULL,NULL,NULL,'5','KANATA','CA',NULL,NULL),
('EDMONTON','AB','Alberta','T6H 0J5',NULL,'Canada',NULL,NULL,NULL,NULL,'7','EDMONTON','CA',NULL,NULL),
('EDMONTON','AB','Alberta','T6H 0J6',NULL,'Canada',NULL,NULL,NULL,NULL,'7','EDMONTON','CA',NULL,NULL)

SELECT * FROM [data_log]

UPDATE [data_log]
       SET [data_log].[h_data] = c.[state_abbr]
      FROM [data_log] d
INNER JOIN [CityStateInfo] c
        ON d.[h_data] = c.[state_long]
     WHERE d.[field] = '[MailingState]'
       AND LEN([h_data]) > 3
       AND [h_data] IS NOT NULL

SELECT * FROM [data_log]

SELECT * FROM [data_log] d
INNER JOIN [CityStateInfo] c
        ON d.[h_data] = c.[state_long]
     WHERE d.[field] = '[MailingState]'
       AND LEN([h_data]) > 3
       AND [h_data] IS NOT NULL

id dataID field sf_data h_data score action
----------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ --------------------------------------------------------------------------------------------------------------------------------
1 3605013844 [MailingCity] Flat Flatt NULL NULL
2 3605013844 [MailingState] KY Kentucky NULL NULL
3 3605013844 [MailingZIP] 41301 41301 NULL NULL
4 1874281127 [MailingCity] EDMONTON Edmonton NULL NULL
5 1874281127 [MailingState] AB Alberta NULL NULL
6 1874281127 [MailingZIP] T6M 2K1 T6M 2K1 NULL NULL
7 2077170855 [MailingCity] Van Buren Point Van Buren Point NULL NULL
8 2077170855 [MailingState] NY New York NULL NULL
9 2077170855 [MailingZIP] 14166 14166 NULL NULL
10 1874281127 [MailingState] PA Ontario NULL NULL
11 1874281127 [MailingState] IL Missouri NULL NULL
id dataID field sf_data h_data score action
----------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ --------------------------------------------------------------------------------------------------------------------------------
1 3605013844 [MailingCity] Flat Flatt NULL NULL
2 3605013844 [MailingState] KY KY NULL NULL
3 3605013844 [MailingZIP] 41301 41301 NULL NULL
4 1874281127 [MailingCity] EDMONTON Edmonton NULL NULL
5 1874281127 [MailingState] AB AB NULL NULL
6 1874281127 [MailingZIP] T6M 2K1 T6M 2K1 NULL NULL
7 2077170855 [MailingCity] Van Buren Point Van Buren Point NULL NULL
8 2077170855 [MailingState] NY NY NULL NULL
9 2077170855 [MailingZIP] 14166 14166 NULL NULL
10 1874281127 [MailingState] PA ON NULL NULL
11 1874281127 [MailingState] IL MO NULL NULL
id dataID field sf_data h_data score action City State_abbr State_long Zip County Country Longitude Latitude StateFIPS CountryFIPS TimeZone cleanCity Country_abbr foreignCity foreignState
----------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- -------------------------------------------------- -------------------- -------------------------------------------------- -------------------------------------------------- --------------- --------------- ---------- ----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------