Link to home
Start Free TrialLog in
Avatar of Micky26
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
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

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!
Avatar of Micky26
Micky26

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?

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

Avatar of Micky26

ASKER

Correct
Avatar of Micky26

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
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Micky26

ASKER

Worked perfectly, thank you so much...

It's always awesome to get another set of eyes on things.