Andriy Pyrozhenko <andriy(dot)pyrozhenko(at)vanjaonline(dot)com> writes:
> CREATE RULE del_jobs_del_activity AS ON DELETE TO jobs DO DELETE FROM activities WHERE job_id = OLD.job_id;
> DELETE FROM jobs WHERE job_id IN (SELECT DISTINCT job_id FROM
> activities WHERE load_no = 123)
> This query does not delete the record from jobs but delete the
> record by rule.
I think you're going to need to use a trigger, instead. The
rule-generated query runs first, and then by the time you get to the
actual DELETE FROM jobs, the SELECT DISTINCT subquery doesn't find any
rows ...
regards, tom lane