RE: SQL Concepts - Header and Detail

From: Pete Helgren

Update header set header.qty = (select sum(qty) from detail group by
ssn,job,ped order by ssn,job,ped)
**and here is were I get uncertain **
where exists
(select 1 from detail where header.ssn = detail.ssn, header.job =
detail.job, header.ped = detail.ped)

Is the "where exists" clause the right choice ? Does the syntax above
make sense?

It depends on what you are trying to do, Pete! Think of it this way: the
WHERE clause tells SQL which records to update in the header file. I fyou
don't have one, every record in the header gets updated, regardless of
whether or not detail records exist. The WHERE EXISTS clause you have will
only update header records if one or more detail records exist.

So ask yourself, what will happen without the WHERE EXISTS clause? Well,
what will happen is that the header.qty field will get set to NULL. This
may or may not be the result you want. You have various options.

1. By adding a simple COALESCE clause, you can set those fields to zero.

Update header set header.qty = coalesce((select sum(qty) from detail
group by ssn,job,ped order by ssn,job,ped), 0)

2. Update only records with matching detail.

That's what you've done with your WHERE EXISTS clause. The problem with
this is that header.qty won't get updated for header without detail. This
could be a problem if, for example, the qty field is non-zero and then you
delete the only detail record. Now you will have a non-zero qty field but
no detail records. Not good.

So there you have it. Set processing is not the easiest thing in the world
to get your head around, but once you understand some of the nuances, it's
very, very powerful.

Joe