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)

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!
Wells AndersonCEOAsked:
Who is Participating?
ralmadaConnect With a Mentor Commented:
try this

;with CTE as (
      select *, row_number() over (partition by CondID order by CondID) rn
      from Children
update CTE
set ChildNo = rn
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.
Wells AndersonCEOAuthor Commented:
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.
Wells AndersonCEOAuthor Commented:
Thank you! This is a very elegant solution to the question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.