Link to home
Start Free TrialLog in
Avatar of Wells Anderson
Wells AndersonFlag for United States of America

asked on

SQL Query - How to update Children records with child numbers

Using MS SQL Server 2005 Express - T-SQL

I have a Contacts table and a Children table. Each Contact has 1 or more Children. I want to assign a Child number (ChildNo) to each Child record. For example, if Contact Jones has three Children, they would get Child numbers 1, 2 and 3. If Contact Smith has two Children, they would get Child numbers 1 and 2.
Each Children record has a ConID field valued with the sysid of its parent Contacts record.

Contacts and Children already have unique IDs (sysid and sysid).
Contacts need ContactNo numbers, so I wrote this query to assign them:

DECLARE @counter int
SET @counter = 10001
UPDATE Contacts
SET @counter = (@counter + 1),
ContactNo = CONVERT(char(5),@counter)
WHERE sysid IN
-- Must use TOP statement here
 (SELECT TOP (4418) sysid
  FROM Contacts
  WHERE ContactNo < 1  -- Fill in ContactNo only in records without one
  ORDER BY last_name ASC, first_name ASC)
GO

Can you help me write a query to update each record in Children with a ChildNo value? Fortunately I know that every Contact has at least one Children record. I don't need a bunch of error checking in the query. Thank you!
Avatar of AndrewFisher
AndrewFisher

How about:
update Children set ChildNo = (select count(*) from Children as c2 where c2.ConID=Children.ConId and c2.sysid <= Children.sysid)

You may run into problems with an error about updating a mutating table, in which case you would need to use a temporary table such as
create table temp as
  select sysid, count(*) as cnt from Children join children as c2 on Children.ConID = c2.ConID
    where c2.sysid <= Children.sysid;
update Children set ChildNo = (select cnt from temp where temp.sysid = Children.sysid);

If your tables are large you might want to create an index on temp's sysid column before executing the update.
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Wells Anderson

ASKER

I think ralmada's answer is exactly what I need, but want to test it before I Accept as Solution. I did not realize you can create a temporary table on the fly like that. Very useful! Also using row_number and partition by is very clever.
Thank you! This is a very elegant solution to the question.