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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!
Micky26Author Commented:
I added the Column Names to the key columns, if you need all let me know. Data.xlsx
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

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Micky26Author Commented:
Correct
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.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Micky26Author Commented:
Worked perfectly, thank you so much...

It's always awesome to get another set of eyes on things.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.