• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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 Anderson
Wells Anderson
  • 2
1 Solution
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.
try this

;with CTE as (
      select *, row_number() over (partition by CondID order by CondID) rn
      from Children
update CTE
set ChildNo = rn
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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now