SQL Query help

Posted on 2009-12-23
Last Modified: 2012-05-08
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.
Question by:FingPhoto
    LVL 26

    Expert Comment

    try this
    SET P1.Parent = P2.Key
    FROM People P1
    	INNER JOIN People P2 ON P1.Index = P2.Parent

    Open in new window

    LVL 10

    Accepted Solution

    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

    LVL 51

    Expert Comment

    maybe this

    update people
    set parent = (select key from people x where x.index=p.parent)

    Expert Comment

    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.

    Key Parent_Name
    1     Joe
    2     Jim
    3     Sara

    Key  Child_Name
    1    Joe
    2     Sara
    3     Michael

    Key ParentName ChildName
    1      Joe                   Sara
    2      Jim                    Joe

    Now it will be easier to query your database by selecting fields from multiple tables

    Author Closing Comment

    This one worked perfectly.  Thank you.

    Author Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now