Madhavan, I have created a similiar table and inserted the data as follows :-
===================================================== CREATE TABLE UT ( U NUMBER(4), S NUMBER(4), G NUMBER(4) ); INSERT INTO UT VALUES(2005,1012,1010); INSERT INTO UT VALUES(2005,1012,1011); INSERT INTO UT VALUES(2006,1013,1010); INSERT INTO UT VALUES(2007,1017,1016); INSERT INTO UT VALUES(2008,1018,1010); INSERT INTO UT VALUES(2008,1018,1011); INSERT INTO UT VALUES(2009,1019,1016); INSERT INTO UT VALUES(2001,1020,1010); INSERT INTO UT VALUES(2001,1020,1011); COMMIT; =========================================================== this query will identify all the security groups and the minimum security group id of the "identical" one ... SELECT DISTINCT S2.S ORIGINAL_SG, /* original security group */ S3.S EQUIV_SG /* equivalent security group */ FROM ( SELECT S, COUNT(*) RECS FROM UT GROUP BY S ) S1, /* security groups and their group counts - table1 */ ( SELECT S, COUNT(*) RECS FROM UT GROUP BY S ) S2, /* security groups and their group counts - table2 */ ( SELECT DISTINCT S FROM UT ) S3 /* just the unique security groups */ WHERE S1.RECS = S2.RECS /* match the sec. groups with the same record counts */ AND S1.S <> S2.S /* make sure they are NOT the same security group */ AND NOT EXISTS /* make sure they include identical group ids */ ( SELECT G FROM UT WHERE S = S1.S MINUS SELECT G FROM UT WHERE S = S2.S ) AND S3.S = ( /* see note */ SELECT MIN(S) FROM UT WHERE G IN ( SELECT G FROM UT WHERE S = S1.S ) ) /* note : this is to find the minimum value of the security id which has the same group id records as that any of the matching security groups. this minimum value can be used to update the security group ids of all other identical security groups at a later point of time */ ============================================================================ ==== you can either change the query to update all the eligible security id to their corresponding minimum values or generate equivalent update statements using this query and run them as a batch ... HTH ... -----Original Message----- Sent: Thursday, April 03, 2003 1:15 PM To: Multiple recipients of list ORACLE-L Hi, Sorry for reposting. Just wanted to put in a subject... I have been grappling with this for sometime and thought it will be best for others to take a look at it. 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 for 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 - I mean, what is it about a decent email service? -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chelur, Jayadas {PBSG} 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).