Wells Anderson
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Thank you! This is a very elegant solution to the question.
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.