SQL Server Performance

help with update query

Discussion in 'General Developer Questions' started by vimalpercy, Mar 21, 2006.

  1. vimalpercy New Member

    I have to do the following task

    1 i have a table Children
    2 i have a table Parent
    3.another table relates these 2 and i call that ChildParent

    ChildParent contains ChildparentCode ChildrenCode and ParentCode

    Now a child have more than one Parent, but i need to have only one parent assigned to a child

    so i should update the parentcode in childparent table with the latest parentcode from parenttable assigned to that Child.

    thanks in advance




  2. Adriaan New Member

    Not sure why you would want a table "in the middle". Sounds like you can just have the ParentCode added as a FK column on the Child table (since one Child can have only one Parent).
  3. vimalpercy New Member

    You are correct .
    but it is an existing DB from where i need to do the cleaning job
    so i have to make sure that one child have only one parent and that parent is the latest in the parent table
  4. Madhivanan Moderator

    What do you mean by latest? Post some sample data and the result you want

    Madhivanan

    Failing to plan is Planning to fail
  5. Adriaan New Member

    Add a UNIQUE constraint on ChildCode.
  6. vimalpercy New Member

    latest = the parent code assigned to that child of late

    max(parentcode)
    Childcode parentcode
    1 3
    1 4
    1 5

    now parent with code 5 will be correct and have the uptodate information

  7. Madhivanan Moderator

    No need to update. Just use this for display purpose

    Select childNode, max(parentnode) from children group by childNode

    Madhivanan

    Failing to plan is Planning to fail
  8. Adriaan New Member

    The highest parent code is not necessarily the 'latest' one.

    But if that is what you need to do, then okay ...

    DELETE FROM ChildParent A
    WHERE A.ParentCode IN
    (SELECT B.ParentCode FROM ChildParent B
    WHERE B.ChildCode = A.ChildCode AND B.ParentCode > A.ParentCode)

    And then add the UNIQUE constraint on ChildCode.
  9. vimalpercy New Member

    yes Madhivanan Sir, that is a very simple solution where even if the child dont have more than one parent
    it still fetch the correct parent.

    Now another issue but same as this one

    I have Child Table, Parent Table and ChildparentTable

    Now every parent have a IDENTITYCARD No.

    Assume parent Mr ABC have 3 Children at the moment he has been put as

    CODE NAME IDNO
    100 Mr ABC x00001
    1001 Mr ABC x00001
    1002 Mr ABC x00001

    So his name and IDNO will be same but have different CODE but IDNO Is defenitely same.

    Now the parent is linked in ChildParent table like this
    CHILDCODE ParentCode
    1 100
    2 1001
    3 1002

    Now Ideally what i want to do is make the parentcode for child 1,2 and 3 = 1002 ( the latest parent Code)

    How it is possible to update the parent code with 1002 for all 1,2 and 3 child by grouping the records based on IDNO from Parent table

    thanks in advance
  10. Adriaan New Member

    Are you sure about what the columns represent? System looks like it has a problem that requires a solution, not just a cosmetic make-over!
  11. vimalpercy New Member

    yes the old system had a different logic which is totally messy and now want to change all those things into a new model but have to keep the old data.
    so to pull those old data need to do a routine.
  12. Adriaan New Member

    You need a couple of translation tables

    Like here -

    CODE NAME IDNO
    100 Mr ABC x00001
    1001 Mr ABC x00001
    1002 Mr ABC x00001

    - you need to add a NEWCODE column, same as CODE, then run this update:

    UPDATE P1
    SET P1.NEWCODE = (SELECT MAX(CODE) FROM Parent P2 WHERE P2.IDNO = P1.IDNO)
    FROM Parent P1

    - and join on CODE, but select the NEWCODE column when moving the data to the new system.
  13. mmarovic Active Member

    I would rather design new proper data structure and then copy and transform data from the old structure into new one. That way you leave old (source) data unaffected, so in case of error you can fix transformation and redo migration. In other words I would:

    1. Create new structure the way Adriaan suggested.
    2. insert rows from old 3 tables into 2 new tables.
  14. vimalpercy New Member

    Ok Adriaan.
    I will add a new table and grab those info into that table and then
    create new relationship table
  15. vimalpercy New Member

    Aadrian I think it is possible to have the translation table 1 done pulling parents

    but how to pull children of duplicated parents and assign to correct parents in translation table 1?

    Ideally TranslationTable1 would be
    IDNO PARENTCODE

    OLDtable
    IDNO PARENTCODE NAME

    So i want to pull records where IDNO are same and parentcode not same
    so that i get other parentcodes and pass that parentcodes to pull Child information
    in parentchild relation table and then assign to another translation table
    with Parentcode Childcode

  16. Adriaan New Member

    Something like this ...

    SELECT Child.ChildCode, Parent.NEWCODE
    FROM Child INNER JOIN ParentChild ON Child.ChildCode = ParentChild.ChildCode
    INNER JOIN Parent ON ParentChild.ParentCode = Parent.ParentCode
    GROUP BY Child.ChildCode, Parent.NEWCODE
  17. vimalpercy New Member

    I want to grab the child belongs to other parentcode with same IDNO as that of NewParentcode
    and assign to newparentcode.

  18. vimalpercy New Member

    select A.parentCode.A.CHildcode, B.IDNO from ParentCHild A,
    (select ParentCode,IDNO from Parent where parentcode not in (select Newparentcode from NEwParenttable)
    and IDNO in (select IDNO from NewparentTable)) B
    where A.Parentcode=B.parentcode

    Is this method correct
  19. Adriaan New Member

    You're already joining on the old parentcode, selecting the new parentcode. Not sure that I understand what you're trying to do?
  20. vimalpercy New Member

    the new parentcode =latest parentcode

    so i am linking the IDNO to pull group all the parents with similar IDNo
    and then remove the NEWparentcodes from that list and pull all parentcodes
    other than that,and search for the children for those parents and make
    a view with childcode parentcode and IDNo

    so that i can use the IDNo to change the parentcode of the new created view
    with newPARENTCODE.
  21. Adriaan New Member

    Since you are moving the data into a new structure, how come you're still using a ParentChild table? Just add a ParentCode FK column to the ChildTable.

Share This Page