1

I have a select statement as below:

DECLARE @delimiter VARCHAR(50)
SET @delimiter=':'
;
WITH CTE AS
( 
    SELECT 
        [pt],
        CAST('<M>' + REPLACE([pt], @delimiter , '</M><M>') + '</M>' AS XML) 
        AS [Employee Name XML]
    FROM  [mytable] 
)
SELECT
     [Employee Name XML].value('/M[1]', 'varchar(50)') As [PT],
     [Employee Name XML].value('/M[2]', 'varchar(50)') As [IP],
     [Employee Name XML].value('/M[3]', 'varchar(50)') As [BR]

FROM CTE
GO

it returns columns named PT, IP and BR. Now, I want to use these values to update the same table i.e. mytable to set the columns PT/IP and BR. How can I do that in SQL?

sticky bit
  • 36,626
  • 12
  • 31
  • 42
tavier
  • 1,744
  • 4
  • 24
  • 53
  • Please add a note on which SQL dialect you are using. – feeela Jun 07 '18 at 12:35
  • From the missing semicolons, the `[]` and the `GO` I'm guessing that's for SQL Server and added the tag. – sticky bit Jun 07 '18 at 12:38
  • I think you might want to read [this](https://stackoverflow.com/questions/982919/sql-update-query-using-joins). It might be usable to achieve what you want (join the CTE to the update). – sticky bit Jun 07 '18 at 12:44
  • You need to output a column to identify each record to update if the combination of PT,IP and BR is not unique. – Ross Bush Jun 07 '18 at 12:44

0 Answers0