Hi, I have been grappling with this for sometime....
I have a table a_user_groups USER_ID SECURITY_GROUP_ID GROUP_ID ---------- ----------------- ---------- 1005 1012 1010 1005 1012 1011 1006 1013 1010 1007 1017 1016 1008 1018 1010 1008 1018 1011 The security_group_id currently is uniquely generated every time a user is added and a group_id is associated with the user_id. For eg: user_id 1005 is associated with groups 1010 and 1011. User 1008 has the same combination but the security_group_id is generated differently. The generation happens from C code and there is an option to correct the problem in the C code but I am trying to see if I can prevent that The requirement is that user_id 1008 and any other users with the same group_id combination should have the same security_group_id 1012, basically the first occurrence for the combination. In the case of user_id 1006 the value for security_group_id is 1013 and the group_id the user_id belongs to is 1016. So all the user_ids with a group_id association of 1016 (1016 in a combination does not count) down the line will have to be updated to 1013. I found a solution for the case where I associate a group to a user_id in this existing table by creating another table that converted the above table into a hierarchy Table b_hier_user_groups USER_ID CGID PARENT_VALUE CHILD_VALUE ---------- ---------- ------------ ----------- 1005 1012 1010 1005 1012 1010 1011 1006 1013 1010 1007 1017 1016 1008 1018 1010 1008 1018 1010 1011 Then using a PL/SQL script I generated the tree using sys_connect_by_path I determined if the user had a path that already. For eg: in the above case if the user 1006 was being associated with group_id 1011, then I would check the exsiting trees to see if there was a path already as in 1010,1011. In this case it does exist and the cgid (equivalent to security_group_id in the above table) is 1012 and update the user_id 1006 to cgid 1012. But I am not for some reason able to apply this solution to the existing rows. I have a feeling that I am missing something simple.... The requirement that I am grappling with is to update the values in the existing table. I can get the table b_hier_user_groups created from the a_user_groups. Please let me know if you need more information The table structures are as below a_user_groups Name Null? Type ----------------------------------------- -------- ---------------------------- USER_ID NOT NULL NUMBER SECURITY_GROUP_ID NOT NULL NUMBER GROUP_ID NOT NULL NUMBER Table b_hier_user_groups Name Null? Type ----------------------------------------- -------- ---------------------------- USER_ID NUMBER CGID ---> same as security_Group_id from above) NUMBER PARENT_VALUE NUMBER CHILD_VALUE NUMBER Thanks for your time and help in advance. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Same, same, but different… -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).