Update field with values from a select query/subquery in SQL

Question

Hello I need to select a specific string form a field one table and then populate another field in a different table in the same SQL database. I am extracting the date from a field. The characters are between two underscores. I was able to create the Select statement correctly using guidance from a previous post in Stack, but when I try to meld it with the update statement an error is thrown. I checked both fields they are of the same type NVARCHAR.

Thanks in advance.

Error that is thrown = Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

UPDATE [DMSEngine_HR].[dbo].[IndexForm_Legiant]
SET [DMSEngine_HR].[dbo].[IndexForm_Legiant].Date_Field=
 (SELECT [DisplayName],
   CASE
   WHEN CHARINDEX('_',(SUBSTRING([DisplayName],CHARINDEX('_',[DisplayName],1)+1,99))) = 0
   THEN LTRIM(RTRIM(SUBSTRING([DisplayName],CHARINDEX('_',[DisplayName],1)+1,99)))
   ELSE LTRIM(RTRIM(SUBSTRING(

    /* <text>> */ [DisplayName],
    /* <start > */      CHARINDEX('_',[DisplayName],1) + 1,
    /* <length> */      CHARINDEX('_', SUBSTRING([DisplayName],
                        CHARINDEX('_', [DisplayName], 1) + 1, 99)) - 1
        ) ) )

    END    AS Result_string

FROM [DMSEngine_HR].[dbo].[Document]
   where DocumentPath like'%legiant%')
   FROM [DMSEngine_HR].[dbo].[IndexForm_Legiant]  INNER JOIN  [DMSEngine_HR].[dbo].[Document]
   ON [DMSEngine_HR].[dbo].[Document].DocumentID = [DMSEngine_HR].[dbo].[IndexForm_Legiant].DocumentID```

Answer 1

I think this is what you are looking for, your query has couple of issues , so I'm not sure what are column or columns you are trying to update

UPDATE 
    l
SET l.Date_Field=
   CASE
   WHEN CHARINDEX('_',(SUBSTRING([DisplayName],CHARINDEX('_',[DisplayName],1)+1,99))) = 0
   THEN LTRIM(RTRIM(SUBSTRING([DisplayName],CHARINDEX('_',[DisplayName],1)+1,99)))
   ELSE LTRIM(RTRIM(SUBSTRING(

    /* <text>> */ [DisplayName],
    /* <start > */      CHARINDEX('_',[DisplayName],1) + 1,
    /* <length> */      CHARINDEX('_', SUBSTRING([DisplayName],
                        CHARINDEX('_', [DisplayName], 1) + 1, 99)) - 1
        ) ) )

    END    
   FROM 
    [DMSEngine_HR].[dbo].[IndexForm_Legiant]  l
    INNER JOIN  [DMSEngine_HR].[dbo].[Document] d
    ON 
    d.DocumentID = l.DocumentID
    AND  DocumentPath like'%legiant%';