question

macostal avatar image
macostal asked

How do I update a Child record field, when there is only one Child record per Parent record?

I have tried and tried. I need to do update a field in the child record, but ONLY if: the parent record has ONLY ONE record in the Child table. I am using TSQL. I already can SELECT parent-child cases where the condition is met. My problem is getting the UPDATE to work. Thank you! Manuel Acosta
tsql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
The site works on voting. Every helpful answer can get a vote by clicking on the thumbs up next to those answers. If any one answer lead to a solution, click on the check mark next to that answer.
0 Likes 0 ·
KenJ avatar image
KenJ answered
This should get you close. The correlated subquery in the WHERE clause makes sure you have exactly one child for the parent you are updating: UPDATE ChildTable SET ChildColumn = 'your value' WHERE ChildId = AND ParentId IN (SELECT ParentId FROM ChildTable WHERE ChildId = GROUP BY ParentId HAVING Count(*) = 1) **[Edit]** Okay, now we know you want to do an update from the parent table, and also that it's not just a singleton update as my previous query assumed, let's touch the query up. First we'll throw out our mythical ChildTable and create a real but temporary one along with a real parent (run these in tempdb): use tempdb select OBJECT_ID as ParentId into #systables from sys.tables select ROW_NUMBER( ) OVER ( ORDER BY object_id ASC ) as ChildId, OBJECT_ID as ParentId, convert(nvarchar(4000), name) as ColumnName into #syscolumns from sys.columns Since we're using sys.tables and sys.columns to spoof our Parent and Child tables, we created #systables with only one column so we know there will be at least ONE ParentId that only occurs a single time in the child table. Now we have a parent table (#systables) and a child table (#syscolumns) that relate on a key (ParentId). Let's update all of the child rows that have a ParentId that only occurs that one time in the whole child table: update #syscolumns set ColumnName = OBJECT_NAME(t.ParentId) + '.' + s.ColumnName from #syscolumns as s inner join #systables as t on s.ParentId = t.ParentId where s.ParentId in ( select ParentId from #syscolumns as s2 group by ParentId having COUNT(*) = 1) Based on your comment about the `FROM` clause, we've included one that we used to update the child table. This query is like the original one up above, but has the various `WHERE` clause elements removed per our discussion in the comments. It also happens to be nearly identical to the one by @Oleg, I just chose to skin the cat using ParentId where he chose ChildId - they both update the same rows. Now that the update is complete, we can query the child table to see that all the rows that represent a single ParentId have been updated from the parent (in this case we just prepend the table name to the column name to simulate your update): select * from #syscolumns where ParentId in ( select ParentId from #syscolumns where ColumnName like '%.%' ) order by ParentId, ChildId I have currently have 7 single column temporary tables so my system returns 7 rows right now. Yours will likely return a different number, but the important thing is that we both see the row where `#systables[some undertermined number of underbars followed by a hex-like number].` has been prepended to `ParentId` And, since this was just a working sample, we drop our temporary tables: drop table #systables drop table #syscolumns Hopefully this helps to clarify the comments thread. @Oleg definitely said it more succinctly (+1)!
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

macostal avatar image macostal commented ·
In my case I don't have a value. I need to simply update all cases where there is only one Child record present per Parent record.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
OK. Just remove `ChildId = ` from both queries (and the `AND` from the outer query)
0 Likes 0 ·
macostal avatar image macostal commented ·
Should I also remove the leftover "WHERE"? As I don't think: "WHERE GROUP BY ..." is allowed. Thank you very much for your time.
0 Likes 0 ·
macostal avatar image macostal commented ·
I believe the script is also missing a FROM statement to declare the usage of the parent table.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
To be honest, the script is missing any details it would need to run against your tables. Without having table definitions, I had to write something rather generic. If you need a FROM statement to update it from the parent table, then you'll definitely need to add one.
0 Likes 0 ·
Oleg avatar image
Oleg answered
If you dissect Ken's script (with slight modifications) then it comes to something like this: Let's assume that your child table is named ChildTable and it has ChildID uniquely identifying the record and also it has a ParentID which references the column uniquely identifying the record in the parent table. Your script does not need to involve the parent table at all if this is the case. If the child table can have one or more records per each ParentID then one way to find all child records which have just one record per ParentID is to use a GROUP BY with HAVING clause restricting the number of records in the group to just one. This makes the update statement to look like this:
update ChildTable
set
    ColumnToUpdate = 'some value'
    where ChildID in (
        select min(ChildID) ChildID -- or use max(ChildID), same thing
            from ChildTable
            group by ParentID
            having count(1) = 1 -- only get the records which have
                                -- just one child record per parent
    );
go
The select statement inside of the where clause selects the ChildID of those child records which have only one record per ParentID. Thus, it does not matter whether you use **min(ChildID)** or **max(ChildID)**, it is the same thing courtesy of the HAVING count(1) = 1 predicate. Hope this helps, Oleg
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

macostal avatar image macostal commented ·
Thank you. Both of you are totally right, there is no need for me to try to link the the two tables. I was also confused on the removal or not of the "Where" on the inner query. This last example shows me that I must. Thank you both very much!
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Glad to help and, to quote @Grant Fritchey, "This site runs off of voting. If any of the answers below [or above in this case] are helpful, you can vote them up by clicking on the thumbs up next to them. Also, you need to identify the best answer by clicking on the check mark next to it." I nominate @Oleg's answer for it's clarity :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.