Updating Multiple Rows of Data based on another Row withing the same table

I have to figure out a way to update multiple rows of data based on another rows of data within the same table.

We will have several rows of data then a row will be added to id the SchoolCode needed for all the data above. A column called U_Configuration will link the SchoolCode to the multiple rows.

I have included a sample order to show how the data is used and to better help assist with the update command.

I am having trouble getting any type of update to look at the SchoolCode and then update all the data where U_Configuration is the same.
What is getting me the most is insuring that the update affects the rows that DocEntry is equal to DocEntry, if have several orders that have the same configurations but the update needs to look at the doc number or docentry then continue to Loop through each line and order seperatly.

I hope someone can help this is a major need  Data.xlsx
Micky26Asked:
Who is Participating?
 
Daniel WilsonCommented:
OK, how's this?

Update D set U_SchoolCode = S.U_SchoolCode
From RDR1 D Inner Join RDR1 S on
  S.DocEntry = D.DocEntry and S.U_configuration = D.U_configuration
  And S.U_SchoolCode is NOT NULL and D.U_SchoolCode is NULL

Open in new window

0
 
Daniel WilsonCommented:
Micky,

I didn't get the column headers in that XLSX file.

Can you tell me the Excel column names (e.g. A, BG, IV, etc.) for the columns you've mentioned?  Specifically, SchoolCode, U_configuration, and DocEntry?

Thanks!
0
 
Micky26Author Commented:
I added the Column Names to the key columns, if you need all let me know. Data.xlsx
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Daniel WilsonCommented:
So ... since SchoolCode on row 15 = 'TXUN', all those rows with the same U_configuration ('B') and same DocEntry number, should also have SchoolCode set to 'TXUN'.

That would be the point of the update, right?

Update D set U_SchoolCode = S.U_SchoolCode
From MyTable D Inner Join MyTable S on
  S.DocEntry = D.DocEntry and S.U_configuration = D.U_configuration
  And S.U_SchoolCode is NOT NULL and D.U_SchoolCode is NULL

Open in new window

0
 
Micky26Author Commented:
Correct
0
 
Micky26Author Commented:
The only table the data exist is the RDR1 table. This is the table I need to update and gather the data from.
0
 
Micky26Author Commented:
Worked perfectly, thank you so much...

It's always awesome to get another set of eyes on things.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.