SQL查询select table table

Question

我有一个SQL表，其中的列在值之间包含一个分隔符(下划线)。

我想要的是将这些值分成几列并更新这个表。

如下所示：

australia_canada 

应该变成

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

到目前为止，我可以选择这些记录，但不需要更新。

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]

在此之后-如何更新现有的表(info_t)？

Answer 1

你可以这样做

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

Answer 2

请看我的例子。我的意思是，你可以将它与你的情况相匹配：

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