SQL query select than update table-CodePudding

I have a SQL table that contain column that includes a delimiter (underscore) between the values.

What I want is to split these values into several columns and update this table.

Like so :

australia_canada

should become

 col1          col2  
 ---------------------
 australia     canada

Until now I am able to select these records but without updating.

SELECT 
    nationality_1,
    REVERSE(PARSENAME(REPLACE(REVERSE(nationality_1), '_', '.'), 1)) AS [nationality_1], 
    REVERSE(PARSENAME(REPLACE(REVERSE(nationality_1), '_', '.'), 2)) AS [nationality_2],
    REVERSE(PARSENAME(REPLACE(REVERSE(nationality_1), '_', '.'), 3)) AS [nationality_3]
FROM 
    (SELECT NEWID() AS [ID], nationality_1 
     FROM [info_t]) AS [t]

After this - how to update the existing table (info_t)?

CodePudding user response：

Please look at my example. I mean you can match it to your situation:

update info_t 
set country_1 = PARSENAME(REPLACE(nationality_1, '_', '.'), 1),
    country_2 = PARSENAME(REPLACE(nationality_1, '_', '.'), 2),
    country_3 = PARSENAME(REPLACE(nationality_1, '_', '.'), 3);

SQL Server update fiddle

CodePudding user response：

You can do it in this way

UPDATE R 
SET R.col1 = p.nationality_2,
    R.col2 = p.nationality_3,
    R.col3 = p.nationality_4 
FROM info_t AS R
INNER JOIN (

 SELECT id, nationality_1
,Reverse(ParseName(Replace(Reverse(nationality_1), '_', '.'), 1)) As [nationality_2]
,Reverse(ParseName(Replace(Reverse(nationality_1), '_', '.'), 2)) As [nationality_3]
,Reverse(ParseName(Replace(Reverse(nationality_1), '_', '.'), 3)) As [nationality_4]
FROM (Select newid() AS [ID], nationality_1 from [info_t]) As [t]

) AS P 
       ON R.pid = P.id 
-- WHERE R.id = '17190'; --you can set filters here