• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

SQL Query help

Hi,
I'm looking for a SQL query to do the following:

I have a table of People that has a column, Parent, containing a pointer to the row who is the parent.
Unfortunately, instead of pointing to the Primary Key, it points to another column called Index. I want to change the contents of the Parent column to point to the correct Key instead.  Sample input is below, and the table I want to end up with is below that.  

Key      Name      Parent      Index
1      Joe      C      A
2      Jim      A      B
3      Jed      B      C
4      Joy      C      D

Key      Name      Parent      Index
1      Joe      3      A
2      Jim      1      B
3      Jed      2      C
4      Joy      3      D

Don't assume that either the Keys or Index appear sequential like in the example above.
I'm rather new to SQL and cannot figure out how to do it.
Thanks.
0
FingPhoto
Asked:
FingPhoto
1 Solution
 
tigin44Commented:
try this
UPDATE P1
SET P1.Parent = P2.Key
FROM People P1
	INNER JOIN People P2 ON P1.Index = P2.Parent

Open in new window

0
 
lofCommented:
look at the following sample. it will do exactly what you need

update A set parent =  B.[key]
from people A 
inner join people B
on A.parent = B.[index] 

Open in new window

0
 
HainKurtSr. System AnalystCommented:
maybe this

update people
set parent = (select key from people x where x.index=p.parent)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rhayderCommented:
You have  a problem with the design of database. You are trying to do too much within your table. You should create two tables Children and Parents. These tables have a many to many relationship because one parent can have many children and one child can have many parents. There is only one way to normalize a many-to-many relationship and that is to create a link table. You should create a third link table. You can call it ParentsChildren if you like.

Parents
Key Parent_Name
1     Joe
2     Jim
3     Sara


Children
Key  Child_Name
1    Joe
2     Sara
3     Michael

ParentsChildren
Key ParentName ChildName
1      Joe                   Sara
2      Jim                    Joe

Now it will be easier to query your database by selecting fields from multiple tables
0
 
FingPhotoAuthor Commented:
This one worked perfectly.  Thank you.
0
 
FingPhotoAuthor Commented:
tigin44: Thank you. I ran yours, with no errors, but it didn't make any changes.  Sorry, I don't know enough yet to be able to debug it.
lof:  This worked. Thanks.
HainKurt: This worked too, but I had already used an earlier response. Thanks.
rhayder:  What I'm really doing is Positions in an organization. There aren't "parents" or "children" but just "positions" where some report to others. So I think 1 Database is correct. Thanks for the response and for all the responses.

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now