[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-03
7
Medium Priority
?
211 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Micky26
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 36904810
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
 

Author Comment

by:Micky26
ID: 36905074
I added the Column Names to the key columns, if you need all let me know. Data.xlsx
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 36905178
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Micky26
ID: 36905227
Correct
0
 

Author Comment

by:Micky26
ID: 36905244
The only table the data exist is the RDR1 table. This is the table I need to update and gather the data from.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 total points
ID: 36905829
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
 

Author Comment

by:Micky26
ID: 36905954
Worked perfectly, thank you so much...

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question