MS SQL 2017. I have been given a task to insert a step into an existing SQL job step that will update the value of a field based on a change in another datasbase and table.
The problem is that the second table involved has 1 to N records.
The field "All_docs_satisfied" is to be set to true when all the individual "papers" for the same studentid are marked as satisfied. The amount of papers in table B may vary as shown in the partial dataset provided
Main table AS A
Studentid, ........, All_docs_Received
Dataset of the second table.AS B
STUDENTID TRAKSET PAPER FLAG RECEIVED SATISFIED 001439569 JAH Second Letter of Recommendation NULL 00 001560125 MSOM College Transcript 1 NULL 00 001560125 MSOM College Transcript 2 NULL 00 001560125 MSOM College Transcript 3 NULL 00 001560125 MSOM College Transcript 4 NULL 00 001560125 MSOM College Transcript 5 NULL 00 001560125 MSOM College Transcript 6 NULL 00 001560125 MSOM College Transcript 7 NULL 00 001560125 MSOM College Transcript 8 NULL 00 001560125 MSOM Resume NULL 00 001385279 FR Official SAT or ACT Scores NULL 00 001372645 Cover Letter 2020-02-07 00:00:00.0001 1 001372645 Resume 2020-02-07 00:00:00.0001 1 001372645 TR Jacksonville Univ Transcript C NULL 00 001372645 TR Otterbein Coll Transcript : C NULL 00 001448667 JAH Roommate Questionnaire NULL 00 Select a.studentid, b.[SATISFIED] from [dbo].[Feith_Emas_Master] as A, Emasrtl.[dbo].[EINDVTRK] AS B where a.all_docs_recieved = '0' and while(a.studentid = b.studentid) Begin Update A Set All_docs_Recieved = 'T' IF (Select b.satisfied is Null) Break IF (Select b.satisfied = 0) Break Else Update A Set All_docs_Recieved = 'T' END