in reply to Re: cleaning up dbi code
in thread cleaning up dbi code

i did use one delete statement and it took ages so i had a go at decomposing it into lots of delete statements.

Basically i want to delete any rows that have a the same value for a particular column (in essence this column has to be unique but cant be a key as it is a calculated value and doesnt start off unique)

I had this code but it takes forever 
delete b.*
from tbl a
inner join tbl b
on a.col1 = b.col1

[download]
so i thought i'll get each row and get its value for the particular column and delete all rows in the db with that value. It's not elegant but will get teh job done.

But i'm not a perl person and the code i wrote for this just looked awful, plus i've done it wrong too :)

Replies are listed 'Best First'.
Re^3: cleaning up dbi code
by Corion (Patriarch) on Nov 26, 2010 at 19:44 UTC

    You don't need a full inner join. Maybe try:

    delete
    from a
where a.col1 in (select distinct col1 from b)

    [download]

    But what I wonder about in your SQL is why you have two references to the same table tbl. Do you really want to delete the whole table?

[reply]
[d/l]
[select]
      why does it delete the whole table? I ran it earlier and it didn't delete the whole table? doesn't it just delete the rows that satisfy the join?
[reply]
        delete b.*
from tbl a
inner join tbl b
on a.col1 = b.col1

        [download]

        This looks like a self-join of the table with itself to me. But if your posted SQL was just sample code, maybe you should work more on workable samples you post. If not, consider thinking more about what you really want to do.

        One standard practice I have when using rm or delete is to run ls respectively select first, and look at the output, and think long and hard about whether the output I see is the stuff I want deleted. Only then I change the select into a delete.

[reply]
[d/l]
[select]

        I'd guess that the rows you have left contain a null in that column, since null doesn't equal null (in the databases I use).

        ...roboticus

[reply]

In Section Seekers of Perl Wisdom