in reply to cleaning up dbi code

Are you aware that SQL databases usually have no fixed row ordering? By what criterion do you want to keep/delete a row?

Also, why don't you roll the deletion into one SQL statement instead?

Replies are listed 'Best First'.
Re^2: cleaning up dbi code
by Anonymous Monk on Nov 26, 2010 at 19:35 UTC
    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 :)
[reply]
[d/l]

      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]

In Section Seekers of Perl Wisdom