delete the output of a join (record in multipe tables)

Question

I use

select * 
from 
    service_instances i 
    inner join 
    service_instance_operations op on i.id = op.service_instance_id 
    inner join 
    service_bindings bind on i.id = bind.service_instance_id  
where i.guid = 'daf67426-129b-4010-832c-692bcfe98f62';

Howto delete this record in all 3 tables? There are primary and foreign key constraints.

It doesn't work to replace "select *" with "delete".

DB schema is CCDB from Cloud Foundry cloud_controller_ng.

Answer 1

You may use WITH construction.

with _deleted_service_instances as (
    delete from service_instances 
    where guid = 'daf67426-129b-4010-832c-692bcfe98f62'
    returning id
)
, _deleted_service_instance_operations as (
    delete from service_instance_operations op
    using _deleted_service_instances i
        where op.service_instance_id = i.id
)
, _deleted_service_bindings as (
    delete from service_bindings b
    using _deleted_service_instances i
        where b.service_instance_id = i.id
)
select null::int as dummy;

UPDATE

I use PostgreSQL 9.0.3 and can't upgrade because of vendor. It was released 2011-01-31. Do you know how the query looks with my version?

I see only one way - is to use three queries:

    delete from service_instance_operations op
    using service_instances i
    where op.service_instance_id = i.id
            AND i.guid = 'daf67426-129b-4010-832c-692bcfe98f62';

    delete from service_bindings b
    using service_instances i
    where b.service_instance_id = i.id
            AND i.guid = 'daf67426-129b-4010-832c-692bcfe98f62';

    delete from service_instances 
    where guid = 'daf67426-129b-4010-832c-692bcfe98f62';