We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


SQL Query - How to update Children records with child numbers

Wells Anderson
Medium Priority
Last Modified: 2012-05-11
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!
Watch Question

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.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview


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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.