Update Query with an Aggregate Function ???

[harleyhead]

I have 2 tables: Header and Line Items - a one to many, respectively. I want to set a flag on the Header to False when there are no Line Items associated with the Header. However, that would require either a Count of Line Items or some other aggregate function - but can't have that in an update query.

How can I accomplish this?

 

[lameid]

Use the IN statement and a sub query OR an outer join to the line items table and is null criteria on the FK in Line items. The former is more SQL server friendly for converting later... I have no Idea which is faster.

First method select statement (you cab convert it to an update in QBE):

Select *
From Header
Where header.key In (Select [Line Items].Key From [Line Items] Where Line Items].Key = header.key Group By [Line Items].Key Having Count(AnyField) = 0)

 

[lameid]

Oops, Not In is more appropriate...

Select *
From Header
Where header.key Not In (select Key From [Line Items])