Re: Informix ISQL problem
>From: brianr@magnus1.com (Bulletin board login)
>Subject: Informix ISQL problem
>Date: Wed, 23 Mar 1994 14:42:44 GMT
>X-Informix-List-Id: <news.6010>
>
>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.
PERFORM is not really the best tool to use for this sort of job, because to
do the job is fiddly, as you'll see. I'd use raw SQL statements.
However, assuming you have a form on the two tables, and that the parts
table is table 1 and that the partscount table is table 2, and that the
link field is defined on a common field, as in:
{
[f000 ]
...
}
f000 =*parts.part_num
= partscount.part_link;
What you have to do is:
* Run the form
* Query for the list of parts to deleted
* For each part to be deleted:
-- Type 2D to switch to the partscount table
-- Delete every partscount item
-- Type M to switch back to the parts table
-- Delete the part
In SQL, roughly what you do is:
DELETE FROM PartCount WHERE Part_Link IN
(SELECT Part_Num FROM Parts WHERE ...);
DELETE FROM Part_Num WHERE ...;
The three dots in the two queries are the same condition.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>