Re: !!! Bad Subquery == Delete ALL !!!

Hey now- >In article <43mepj$dp8@hazel.Read.TASC.COM> > amiller@tasc.com "Andrew Miller" writes: >> Hi there- >> >> I recently executed the following query on my Informix database: >> >> delete from BLOBtable where PageNumber in >> (select PageNumber from DocumentTable where DocumentID=66272)>> >> >> Now as it turns out, there is no PageNumber field in my DocumentTable table, so >> the subquery failed. Instead of returning an error, the Informix engine DELETED> ALL THE BLOBS IN MY BLOB TABLE (imaging my surprise when instead of seeing "11 >> rows deleted" I saw "1700 rows deleted")!!!!! Here's the explanation given to me by Informix tech. support. Unfortunately, the query performed appropriately: ================================================================================ i explained the query is operating properly. the uidnpage column isn't in tbdocument table in the subquery. but since the table it is in, tbmagpage, is bein SELECTed from in the DELETE query, uidnpage will be recognized. now, when the query runs the pointer in the tbdocument will be on the row where uidndoc = 66272, but the pointer in the tbmagpage table will go trhu all the rows, thereby grabbing all the possible values for uidnpage in tbmagpage. those values will then be a part of the IN clause of the DELETE, and since all values of uidnpage were found, all rows in tbmagpage will be deleted. ================================================================================ UGH! This definately goes on my top ten not-so-cool-things that happened to me this week. Andrew +----------------------------------+-----------------------------------+ | Andrew Miller | TASC | | mailto:amiller@tasc.com | 55 Walkers Brook Drive | | (617)942-2000 x2011 | Reading, MA 01867 | +----------------------------------+-----------------------------------+ | Control for smilers can't be bought. | +----------------------------------------------------------------------+