[Dev-sig] Query question for SQL gurus...

[Emerson, Tom (*IC)]

[disclaimer: I'm working with a semi-proprietary SQL "source" that is
/almost/ SQL-90 compliant...]

I've got an odd one (sort of), thought I'd bounce it against the "gurus"
for some insight.

This is for an inventory application, specifically in support of
"physical inventory counting".  One table is the "header" [mostly] which
consists of a "primary key" [a concatenation of the part number, "PI
key", and location] and a few secondary keys [part number by itself, the
"PI key" by itself, etc.]  Here the "PI key" references all the entries
for a specific inventory-counting session.

There is a secondary table of "counted" quantities, which only has the
concatenated "primary key" -- it does NOT have the individual "key"
items as distinct fields  [it could be that this table contains fields
that the designers "forgot" to have in the original table, so in effect
this should "extend" the main table]

The task I have in front of me is to perform a "delete" of any previous
data /for a specific PI/.

Deleting the header is brain-dead trivial: delete from header where
pi-key = <whatever>, but what do I do about the "extension" table?

Is this valid:

   delete from header,extension
   where header.pi-key = <whatever>
     and extension.pri-key = header.pri-key

[translation: as I've not done this before, is deleting from MULTIPLE
tables allowed?]

Barring that, I would (ordinarily) do this:

   delete from extension
    where pri-key in (select pri-key from header where pi-key =
<whatever>)

But there is a downside [which I've discovered]: if the NUMBER OF
ELEMENTS in the "IN(...)" clause exceeds 255, the query engine performs
a SERIAL READ of the table in order to determine "does this qualify?"
[with fewer than 256 elements, I suspect it does a keyed-read per
element]


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.sgvlug.net/pipermail/dev-sig/attachments/20070813/9c573994/attachment.html