SQL Server Performance Forum – Threads Archive
help with update query
I have to do the following task 1 i have a table Children2 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
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).
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
What do you mean by latest? Post some sample data and the result you want Madhivanan Failing to plan is Planning to fail
Add a UNIQUE constraint on ChildCode.
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
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
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.
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
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!
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.
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.
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.
Ok Adriaan.
I will add a new table and grab those info into that table and then
create new relationship table
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
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
I want to grab the child belongs to other parentcode with same IDNO as that of NewParentcode
and assign to newparentcode.
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
You’re already joining on the old parentcode, selecting the new parentcode. Not sure that I understand what you’re trying to do?
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.
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.
]]>