none
Update statement with subquery

    Question

  • Hello:

    Can someone assist me with this, please.

    I'm getting subquery returned more than 1 value from the code below. I realize that Documentrecords_62 table will bring back multible rows and that's expected. For each row I need to do a carriage return, that's why I'm using CHAR(13) and CHAR(10).

    UPDATE Documentrecords_90

           SET Change_Descriptions =

                  (SELECT

                      CAST(ChangeNumber As  varchar(5)) + '     ' + 'WAS: ' +

                      CAST(TextBefore) As  varchar(100)) + '     ' + 'CHANGED TO: ' +

                      CAST(TextAfter As  varchar(100)) + CHAR(13) + CHAR(10) AS TEST

            FROM DOCUMENTRECORDS_62

            WHERE CHGPRODUCTCODE = '0382505' AND CHGREVISION = 'A'

             WHERE PARTPRODCODE = '0382505' AND REVREVISION = 'A'

    Thanks

    Friday, July 11, 2014 8:11 PM

All replies

  • Please try this:

    update A
    set Change_Descriptions = CAST(ChangeNumber As  varchar(5)) + '     ' + 'WAS: ' +
    						  CAST(TextBefore) As  varchar(100)) + '     ' + 'CHANGED TO: ' +
    						  CAST(TextAfter As  varchar(100)) + CHAR(13) + CHAR(10) 
    from Documentrecords_90 as A
    join DOCUMENTRECORDS_62 as B on A.pk = B.pk
    where A.CHGPRODUCTCODE = '0382505' AND A.CHGREVISION = 'A'


    Saeid Hasani [sqldevelop]

    Friday, July 11, 2014 9:15 PM
  • Hi,

    According to your description, we need to know the table structure, data and join relationship of the two tables for further analysis. If you only need to update the Change_Descriptions field in table Documentrecords_90 with the first row from the subquery result, you can use TOP 1 in the subquery as follows.
     
    UPDATE Documentrecords_90
     
           SET Change_Descriptions = 
     
                  (SELECT TOP 1
     
                      CAST(ChangeNumber As  varchar(5)) + '     ' + 'WAS: ' +
     
                      CAST(TextBefore As  varchar(100)) + '     ' + 'CHANGED TO: ' +
     
                      CAST(TextAfter As  varchar(100)) + CHAR(13) + CHAR(10) AS TEST
     
            FROM DOCUMENTRECORDS_62  
            WHERE DOCUMENTRECORDS_62.CHGPRODUCTCODE = '0382505' AND DOCUMENTRECORDS_62.CHGREVISION = 'A' 
                                     
    )
             WHERE PARTPRODCODE = '0382505' AND REVREVISION = 'A'

    However, if the result doesn’t meet your requirement, assume that the two tables contain a primary key respectively, you need to add the addition (DOCUMENTRECORDS_62.pk= Documentrecords_90.pk) in WHERE clause in the subquery. Also you can use the JOIN syntax as Saeid’s post, please note that the syntax should be as follows:
    update A
    set Change_Descriptions = CAST(ChangeNumber As  varchar(5)) + '     ' + 'WAS: ' +
                                                                                                      CAST(TextBefore As  varchar(100)) + '     ' + 'CHANGED TO: ' +
                                                                                                      CAST(TextAfter As  varchar(100)) + CHAR(13) + CHAR(10) 
    from Documentrecords_90 as A
    join DOCUMENTRECORDS_62 as B on A. pk = B.pk
    where A. PARTPRODCODE = '0382505' AND A.REVREVISION = 'A'


    Thanks                                                                                                          
    Lydia Zhang
    Monday, July 14, 2014 3:53 PM
  • Saeid, thanks for responding.

    There's 4 records that make up this record set but it's only bring back the first record.

    Below is the code I'm using: 

    update A
    set Change_Descriptions = CAST(ChangeNumber As  varchar(5)) + '     ' + 'WAS: ' +
      CAST
    (TextBefore) As  varchar(100)) + '     ' + 'CHANGED TO: ' +
      CAST
    (TextAfter As  varchar(100)) + CHAR(13) + CHAR(10)
    from Documentrecords_90 as A
    join DOCUMENTRECORDS_62 as B

    on A.partprodcode = B.chgproductcode and A.RevisionCode = B.chgrevision
    where B.CHGPRODUCTCODE = '0382505' AND B.CHGREVISION = 'A'

    Monday, July 14, 2014 6:14 PM
  • Lydia - thanks for responding.

    Please see my post to Saeid.

    Monday, July 14, 2014 6:18 PM
  • Saeid, thanks for responding.

    There's 4 records that make up this record set but it's only bring back the first record.

    Hi

    Are you looking for tracking all 4 rows data into Change_Descriptions column in Documentrecords_90

    then below code may help you to achieve this

    select * into #temp1 from
    (
    select 1 sno,convert(varchar(200),'')comments ) a
    
    select * into #temp12 from
    (
    select 1 sno,'C1'comments union all
    select 1 sno,'C2'comments union all
    select 1 sno,'C3'comments union all
    select 1 sno,'C4'comments) a
    
    update #temp1 set #temp1.comments= STUFF(
        (SELECT ',' + comments
           FROM #temp12 where sno=#temp1.sno
            FOR XML PATH(''),type).value('.','nvarchar(max)'),1,1,'')
      where sno= 1      
      
         select * From  #temp1

    Not tested, please convert the update to a select before execute and see whether you are getting expected result the execute the update statement , if possible you can remove the ','

    UPDATE Documentrecords_90
           SET Change_Descriptions =
                 STUFF( (SELECT
                      ',' + CAST(ChangeNumber As  varchar(5)) + '     ' + 'WAS: ' +
                      CAST(TextBefore) As  varchar(100)) + '     ' + 'CHANGED TO: ' +
                      CAST(TextAfter As  varchar(100)) + CHAR(13) + CHAR(10) 
            FROM DOCUMENTRECORDS_62 
            WHERE CHGPRODUCTCODE = Documentrecords_90.PARTPRODCODE AND CHGREVISION = Documentrecords_90.REVREVISION
            FOR XML PATH(''),type).value('.','nvarchar(max)'),1,1,'')
             WHERE PARTPRODCODE = '0382505' AND REVREVISION = 'A'


    Thanks

    Saravana Kumar C


    • Edited by SaravanaC Monday, July 14, 2014 7:07 PM
    Monday, July 14, 2014 7:07 PM
  • Saravana, thanks for responding.

    I tried the code below and I'm receiving the following error message:

    Row name '  ' contains an invalid XML identifer as required for by XML; '  '(0x0020) is the first character at fault.

    UPDATE Documentrecords_90
           SET Change_Descriptions =
                 STUFF( (SELECT
                      CAST(ChangeNumber As  varchar(5)) + '     ' + 'WAS: ' +
                      CAST(TextBefore) As  varchar(100)) + '     ' + 'CHANGED TO: ' +
                      CAST(TextAfter As  varchar(100)) + CHAR(13) + CHAR(10) 
            FROM DOCUMENTRECORDS_62 
            WHERE CHGPRODUCTCODE = Documentrecords_90.PARTPRODCODE AND CHGREVISION = Documentrecords_90.REVREVISION
            FOR XML PATH(''),type).value('.','nvarchar(max)'),1,1,'')
             WHERE PARTPRODCODE = '0382505' AND REVREVISION = 'A'
    Best regards
    Tuesday, July 15, 2014 7:13 PM
  • Saravana, thanks for responding.

    I tried the code below and I'm receiving the following error message:

    Row name '  ' contains an invalid XML identifer as required for by XML; '  '(0x0020) is the first character at fault.

    UPDATE Documentrecords_90
           SET Change_Descriptions =
                 STUFF( (SELECT
                      CAST(ChangeNumber As  varchar(5)) + '     ' + 'WAS: ' +
                      CAST(TextBefore) As  varchar(100)) + '     ' + 'CHANGED TO: ' +
                      CAST(TextAfter As  varchar(100)) + CHAR(13) + CHAR(10) 
            FROM DOCUMENTRECORDS_62 
            WHERE CHGPRODUCTCODE = Documentrecords_90.PARTPRODCODE AND CHGREVISION = Documentrecords_90.REVREVISION
            FOR XML PATH(''),type).value('.','nvarchar(max)'),1,1,'')
             WHERE PARTPRODCODE = '0382505' AND REVREVISION = 'A'
    Best regards

    Hi Can you try below

        UPDATE Documentrecords_90
           SET Change_Descriptions =
                 STUFF( (SELECT
                      CAST(ChangeNumber As  varchar(5)) + '     ' + 'WAS: ' +
                      CAST(TextBefore) As  varchar(100)) + '     ' + 'CHANGED TO: ' +
                      CAST(TextAfter As  varchar(100)) + CHAR(13) + CHAR(10)  AS [text()]
            FROM DOCUMENTRECORDS_62 
            WHERE CHGPRODUCTCODE = Documentrecords_90.PARTPRODCODE AND CHGREVISION = Documentrecords_90.REVREVISION
            FOR XML PATH(''),type).value('.','nvarchar(max)'),1,1,'')
             WHERE PARTPRODCODE = '0382505' AND REVREVISION = 'A'
    Refer : http://blogs.lessthandot.com/index.php/webdev/business-intelligence/dealing-with-the-column-name/

    Tuesday, July 15, 2014 7:29 PM
  • I added the "AS [text()]" and still getting the same error message.
    Tuesday, July 15, 2014 7:57 PM