A SQL Question

Hello, I need to have a SQL statement for following case. I have two tables with parent/child relation. Parent also has two columns which are the reference counters e.g. counter1 and counter2 ( don't ask me why there are two counters ). Reference counters of each parent row contains the number of the child rows which refer to this parent. E.g. if parent PK ( Primary key ) has values 100,200,300,400 and child FK ( foreign keys ) have values 100,200,100,100,400,200 then couters for 100 will be 3, couneters for 200 will be 2, counters for 300 will be 0 and counters for 400 will be 1. 1) In a process, I first reset all the parent counters to zero. Then I need to increment the counters in parent row by the count of child row. update parent set counter1 = counter1 + ( select count(*) from child where child.foreign_key = parent.primary.key ) Now I have to increment counter1 and counter 2 by same amount. How do I do that ** without ** using second query for counter2 or without using another inner query ( select count(*) from child where child.FK = parent.PK ) for counter2 2) If child has multiple foreign keys per row, and FKs in a row can have different values, how do I do above ? Responce by reply to this board or by email will be appreciated. It is O.K to have different solutions for different RDBMS. Thanks, -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

On Sat, 09 Jan 1999 00:51:35 GMT, badnam2000@hotmail.com wrote: >1) In a process, I first reset all the parent counters to zero. Then I need to >increment the counters in parent row by the count of child row. > >update parent set counter1 = counter1 + ( select count(*) from child where >child.foreign_key = parent.primary.key )> >Now I have to increment counter1 and counter 2 by same amount. How do I do >that ** without ** using second query for counter2 or without using another >inner query ( select count(*) from child where child.FK = parent.PK ) for >counter2 In Oracle, you can do the following: UPDATE parent SET (counter1, counter2) = (SELECT parent.counter1+COUNT(*), parent.counter2+COUNT(*) FROM child WHERE child.FK = parent.PK); >2) If child has multiple foreign keys per row, and FKs in a row can have >different values, how do I do above ? I don't understand what you are actualy asking with this one. Mybe it will be more clear if you could provide a practical example, even if it is completely hypothetical. HTH, Jurij Modic <jmodic@src.si> Certified Oracle7 DBA (OCP) ================================================ The above opinions are mine and do not represent any official standpoints of my employer

In article <3697d5c0.9646962@news.arnes.si>, jmodic@src.si (Jurij Modic) wrote: > On Sat, 09 Jan 1999 00:51:35 GMT, badnam2000@hotmail.com wrote: > > >1) In a process, I first reset all the parent counters to zero. Then I need to > >increment the counters in parent row by the count of child row. > > > >update parent set counter1 = counter1 + ( select count(*) from child where > >child.foreign_key = parent.primary.key )> > > >Now I have to increment counter1 and counter 2 by same amount. How do I do > >that ** without ** using second query for counter2 or without using another > >inner query ( select count(*) from child where child.FK = parent.PK ) for > >counter2 > > In Oracle, you can do the following: > > UPDATE parent SET (counter1, counter2) = > (SELECT parent.counter1+COUNT(*), parent.counter2+COUNT(*) > FROM child WHERE child.FK = parent.PK);> > >2) If child has multiple foreign keys per row, and FKs in a row can have > >different values, how do I do above ? > > I don't understand what you are actualy asking with this one. Mybe it > will be more clear if you could provide a practical example, even if > it is completely hypothetical. > # 1 assumed that child has one FK column(s). A child can have multiple occurences of the FKs. E.g. Parent table has Item code as primary key. A child table can have multiple columns as item code, all referening to same parent table ( but not necessarily same row ). I.e. in a child row, one item code can have value "Red Chair" and another item code can have value "Desk". These two refer to different rows in parent. Thanks, -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

On Mon, 11 Jan 1999 19:00:15 GMT, badnam2000@hotmail.com wrote: ># 1 assumed that child has one FK column(s). A child can have multiple >occurences of the FKs. E.g. > >Parent table has Item code as primary key. A child table can have multiple >columns as item code, all referening to same parent table ( but not >necessarily same row ). I.e. in a child row, one item code can have value >"Red Chair" and another item code can have value "Desk". These two refer to >different rows in parent. I'm affraid you won't be able to solve this one without the second correlated subquery, e.g UPDATE parent SET counter1=(SELECT COUNT(*) FROM child WHERE parent.PK = child.FK1), counter2=(SELECT COUNT(*) FROM child WHERE parent.PK = child.FK2); >Thanks, HTH, Jurij Modic <jmodic@src.si> Certified Oracle7 DBA (OCP) ================================================ The above opinions are mine and do not represent any official standpoints of my employer