SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 active in parent if at least one child is active
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zifter
Starting Member

44 Posts

Posted - 03/05/2014 :  07:47:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 03/05/2014 :  08:57:51  Show Profile  Visit webfred's Homepage  Reply with Quote
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



Too old to Rock'n'Roll too young to die.
Go to Top of Page

Zifter
Starting Member

44 Posts

Posted - 03/05/2014 :  09:45:46  Show Profile  Reply with Quote
Brilliant in its simplicity.
Feel embarrassed (again) I couldn't come up with it myself...
Thanks!
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 03/05/2014 :  12:08:26  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000