[Dbix-class] Removing orphan records from linked tables

Nick Prater nick at npbroadcast.com
Wed Sep 12 11:41:09 GMT 2012


I'm getting an error "You can't specify target table ... for update in
FROM clause"

I have two linked tables in a mysql database, from which I am trying to
remove orphan records using DBIC - records in one table that do not have a
corresponding record in the other table.

In plain SQL, I can use the following command:

DELETE events.*
FROM events
LEFT JOIN clips ON (events.event_id = clips.event_id)
WHERE clips.clip_id IS NULL
AND events.status='deleted'

I have tried to achieve the same using DBIC, with the following code:

my $rs = $c->model('DB::Event')->search(
    {
        'clips.clip_id' => undef,
        'me.status'     => 'deleted',
    },
    {
        join => 'clips'
    }
);
$rs->delete;

But that results in an error:

DBIx::Class::ResultSet::delete(): DBI Exception: DBD::mysql::st execute
failed:
You can't specify target table 'events' for update in FROM clause
[for Statement "
DELETE FROM events
WHERE ( event_id IN (
    SELECT me.event_id
    FROM events me
    LEFT JOIN clips clips ON clips.event_id = me.event_id
    WHERE ( (
        clips.clip_id IS NULL
        AND me.status = ?
    ) )
    GROUP BY me.event_id
) )
" with ParamValues:
0='deleted',
]


I am using DBIx::Class v0.08196 with mysql 5.5.24-0ubuntu0.12.04.1, as
packaged with Ubuntu 12.04

Is anybody able to explain what I am doing wrong, or where to find more
infomation?

My reading of the POD and google searches are not helping, which probably
means I'm trying to do something very daft or missing something very
obvious!

Many thanks

Nick





More information about the DBIx-Class mailing list