Micky26
asked on
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
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
ASKER
I added the Column Names to the key columns, if you need all let me know. Data.xlsx
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?
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
ASKER
Correct
ASKER
The only table the data exist is the RDR1 table. This is the table I need to update and gather the data from.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked perfectly, thank you so much...
It's always awesome to get another set of eyes on things.
It's always awesome to get another set of eyes on things.
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!