Re: Informix ISQL problem

brianr@magnus1.com (Bulletin board login) writes: >Hello everyone, I am having a slight problem with two linked tables. I have an >parts table and a partscount table and I need to delete a large number of parts >from using a parameter in a single field. This part works fine exept that the >partscount table needs to be updated as well. I can't seem to find the commands >to coordinate the deletion of the parts and the actual parts count in the other >table. They are both linked by a field called part_link, I just don't know of a >way to do a delete on two table at once. I wasn't able to select both for a >delete at the same time. Oh, and the parameter that I am using for the delete >is only found in the parts table and not in the partscount table. >So, if anyone has any suggestions, It would be greatly appreciated. The DELETE statement itself works on only 1 table at a time, but you can remove the appropriate records from partscount this way: DELETE FROM partscount WHERE part_link IN (SELECT part_link FROM parts WHERE parts_condition); then: DELETE FROM parts WHERE parts_condition; Other details: 1) if you have transaction logging, you can make the deletes part of a logical transaction by surrounding the DELETE statements with BEGIN WORK and COMMIT WORK (see transactions in the SQL manuals). 2) OnLine 6.0 has the "cascading deletes" feature which would allow you to perform this function. 3) You could create a DELETE TRIGGER in the parts table to take care of the partscount table if you have version 5.01 (I think) of the Database Engine. Refer to Triggers in the manuals for more. ============================================================ Dennis J. Pimple Senior Consulting Services Engineer Informix Software Inc Denver Colorado USA dennisp@informix.com Voice:303-850-0210 Fax:303-779-4025