0

I have 2 tables Awards_Nominations and custom_1, and I need to update Awards_Nominations.Add14 column with the concatenations of custom_1.awardNames.

Conditions should have :

Add1 >= GPA

Majors like Add3 or 'AllMajors'

AcademicReq like Add2 or 'High School Student'

Universities like Add4 or 'AllUniversities'

sample Awards_Nominations

sample custom_1

I tried with the update statement inner join but it does not update.

UPDATE [dbo].[Awards_Nominations]
   SET Add14=Add14+','+awardName
  from [Awards_Nominations] a
  inner join custom_1 on right([Add1],3)>=GPA and (Majors like '%'+Add3+'%' or Majors='AllMajors') 
  and (AcademicReq like '%'+Add2+'%' or 'High School Student' like '%'+AcademicReq+'%') and (Universities like '%'+Add4+'%' or Universities='AllUniversities')
  where n_AwardID=4 and ApprovalStatus='final' and Add1<>'' 
GO

My end goal is to have something like below:

sample updated Awards_Nominations

Belle
  • 1
  • 1
  • 2
    You should really change the links to text data. Asking someone to click on the pictures, then notate the text, and then finally develop a solution is a lot to ask. – jw11432 Nov 03 '20 at 22:41
  • 1
    The first step is to get your query working as a select, that will show you whether your conditions work as expected. And when you alias the table being updated, you should then be updating the alias i.e. you should have `update a` – Dale K Nov 03 '20 at 22:41

1 Answers1

1

With an update that matches multiple records, the last update wins. The source column value is not updated for use as input later. (It's sort of like the "inserted" table in a trigger that represents the data before the query.) You want to concatenate data from multiple rows into a single value. There are plenty of examples out there. I prefer XML myself. I will try STRING_AGG the next time I need to do this.

How to concatenate text from multiple rows into a single text string in SQL server?

Randy in Marin
  • 1,108
  • 4
  • 9