[Dbix-class] Removing orphan records from linked tables

Len Jaffe lenjaffe at jaffesystems.com
Wed Sep 12 14:30:10 GMT 2012


On Wed, Sep 12, 2012 at 7:41 AM, Nick Prater <nick at npbroadcast.com> wrote:

> 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 =3D clips.event_id)
> WHERE clips.clip_id IS NULL
> AND events.status=3D'deleted'
>
>
> DELETE events.*
Shouldn't that just be 'DELETE FROM events'  ?

> WHERE clips.clip_id IS NULL
Is clip_id the PK of clips?

How does clips.clip_id come to be null?

What was the design decision that led to not having FK constraints to
prevent orphans?

Len.

-- =

lenjaffe at jaffesystems.com   614-404-4214
www.volunteerable.c<http://www.volunteerable.net>
om
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar <http://www.greenbartraining.org/>: Grubmaster: 2012-2009, Grub
Asst: 2008, Trained: 2007.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120912/7ee=
1715e/attachment.htm


More information about the DBIx-Class mailing list