active in parent if at least one child is active

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

active in parent if at least one child is active

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-03-05 : 07:47:21

I have two tables with a classic parent-child foreign key relation.
They both have an 'Active' column.
I want to update the active column of the parent table and set it to true if at least one of its child records is set to active otherwise it should be updated to false.

I've managed getting the result with the following. First setting all parent active columns to false. And a second update statement to set it to true if at least one child is set to active.

My question is if there is a better way (for example using only one update statement).

declare @parent table(ID int, Active bit)
declare @child table(ID int, FKParent int, Active bit)

insert into @parent
values
(1,0),
(2,1),
(3,1)

insert into @child
values
(1,1,0),
(2,1,1),
(3,1,0),
(4,2,0),
(5,2,1),
(6,3,0),
(7,3,0)

select *
from @parent

select *
from @child

update p
set p.Active = 0
from @parent as p

update p
set p.Active = 1
from @parent as p
where exists
(
  select distinct c.FKParent
  from @child as c
  where c.FKParent = p.ID
  and c.Active = 1
)

select *
from @parent

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-03-05 : 08:57:51

[code]update p
set Active =
case
when exists (select * from @child c where c.FKParent = p.ID and c.Active=1) then 1
else 0
end
from @parent p
[/code]

Too old to Rock'n'Roll too young to die.

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-03-05 : 09:45:46

Brilliant in its simplicity.
Feel embarrassed (again) I couldn't come up with it myself...
Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-03-05 : 12:08:26

Don't feel embarrassed

Be proud to be the kind of dude who is able to ask a question this way.

I love to see table structure, sample data and wanted output so much but it is a rare event here in the forums...

cheers
Fred

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources