Re: SQL Concepts - Header and Detail

Thanks Joe.

Yes, I only want to update header records where detail exists so I shouldn't be ending up with any headers with no detail (thanks for the coalesce tip though, might come in handy). So I am assuming the "where exists" is correct.

Yep. This is great stuff and my first forays were just simple updates on a single file. The header/detail thing is where things start to get a bit uncertain about what relates to what. More of this is in the future though. It *is* just too powerful to pass up (dangerous, too).

Pete


Joe Pluta wrote:

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