Hi

So I am trying to remove any orphans in the product file.
I am doing this by checking if the "id" exists in both tables.
if it does not then the product record can be deleted.
Cannot get this to work !!

here is my code:

DELETE FROM product, package INNER JOIN package ON product.product_no = package.product_no  WHERE product.product_id <> package.product_id;

any pointers?

Thanks

Kamran

kamran wrote:

> WHERE product.product_id <> package.product_id;

Surely that will get you a many by many match? And delete everything, if it did.

Deleting is always awkward in my experience. Perhaps I just am too nervous about it. My favourite is to have a column which indicates that something is still valid. So you set DeleteRecord to true, and then validate each row, perhaps using the join, and set DeleteRecord to false. At the end, you have all the good records marked as false, and you can safely delete anything still marked as deleteRecord = true.

Now, one other proviso, I found deleting a lot of records in one big go could cause high-availability reads to stall, so I changed to doing a select to get a single record ID, deleting that, pausing, and then deleting another. Since this was in a maintenance thread, the length of time didn't matter, and normal operation carried on around.

Of course you also have to be sure that you aren't deleting records being created, so I will leave that as an exercise for the reader. 8-)

--

Matthew Jones

kamran


>DELETE FROM product, package INNER JOIN package ON product.product_no = package.product_no WHERE product.product_id <> package.product_id;

JOIN syntax and DELETE doesn't work in ElevateDB. Looking at the SQL it looks as though you're trying to delete from two tables simultaneously and I, personally, don't know any syntax in ElevateDB that will accomplish that.

To delete only when there aren't any detail records the following

DELETE FROM product WHERE NOT EXISTS (SELECT package.product_id FROM package WHERE package.product_id  = product.product_id)

should work

Roy Lambert

Matthew

>Deleting is always awkward in my experience. Perhaps I just am too nervous about it. My favourite is to have a column which indicates that something is still valid. So you set DeleteRecord to true, and then validate each row, perhaps using the join, and set DeleteRecord to false. At the end, you have all the good records marked as false, and you can safely delete anything still marked as deleteRecord = true.
>
>Now, one other proviso, I found deleting a lot of records in one big go could cause high-availability reads to stall, so I changed to doing a select to get a single record ID, deleting that, pausing, and then deleting another. Since this was in a maintenance thread, the length of time didn't matter, and normal operation carried on around.

Doesn't setting the deleteRecord column cause the same sort of problem that deleting would do for the high-availability reads?


>Of course you also have to be sure that you aren't deleting records being created, so I will leave that as an exercise for the reader. 8-)

Run the whole thing in a transaction, forget impact on high-availability reads and focus on database integrity!


Roy

Roy Lambert wrote:

> Doesn't setting the deleteRecord column cause the same sort of problem that deleting would do for the high-availability reads?

Not that I am aware of! Or certainly not to the same extent. Or maybe it does. All I know is that I found that a big delete was causing a big pause (where big is not long but significant when you want things to be fast and not hanging threads) so I adopted the alternative as suggested.

Obviously this is just general advice based on the experience I had in one situation.

--

Matthew Jones

Matthew

>> Doesn't setting the deleteRecord column cause the same sort of problem that deleting would do for the high-availability reads?
>
>Not that I am aware of! Or certainly not to the same extent. Or maybe it does. All I know is that I found that a big delete was causing a big pause (where big is not long but significant when you want things to be fast and not hanging threads) so I adopted the alternative as suggested.
>
>Obviously this is just general advice based on the experience I had in one situation.

Way back, with DBISAM, I had a similar problem with my email / newsreader app. I eventually found that if I set all the memo fields to null before deleting the whole process ran faster than just deleting. It had something to do with the way freed up blocks were being returned to the pool. Tim did fix it but its one of the things I still try if a delete is taking to long.

If you can remember the structure of that table did it have large CLOB or BLOB columns or lots of indices? Just being curious.

Roy Lambert

> If you can remember the structure of that table did it have large CLOB or
> BLOB columns or lots of indices? Just being curious.

It did, and now you mention it it was DBISAM. EDB may be better now. Worth
timing or waiting until it is a problem perhaps.


--
Matthew Jones

Thanks to you both

Roy your code works nicely .. it  did the trick ! ..thanks

Have a good weekend


Roy Lambert wrote:

kamran


>DELETE FROM product, package INNER JOIN package ON product.product_no = package.product_no WHERE product.product_id <> package.product_id;

JOIN syntax and DELETE doesn't work in ElevateDB. Looking at the SQL it looks as though you're trying to delete from two tables simultaneously and I, personally, don't know any syntax in ElevateDB that will accomplish that.

To delete only when there aren't any detail records the following

DELETE FROM product WHERE NOT EXISTS (SELECT package.product_id FROM package WHERE package.product_id  = product.product_id)

should work

Roy Lambert

Matthew,

<< Not that I am aware of! Or certainly not to the same extent. Or maybe it does. All I know is that I found that a big delete was causing a big pause (where big is not long but significant when you want things to be fast and not hanging threads) so I adopted the alternative as suggested. >>

The important distinction here is: are you deleting a large number of rows, or are you simply executing a DELETE statement that isn't optimized ?

If the situation is the former, then there isn't much you can do without, as Roy indicates, encountering the possibility that the database may be in a "half-done" state at some point.  But, with deletes you can sort-of tolerate this by simply running the individual deletes until no more candidate rows are available to be deleted.

Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< Way back, with DBISAM, I had a similar problem with my email / newsreader app. I eventually found that if I set all the memo fields to null before deleting the whole process ran faster than just deleting. It had something to do with the way freed up blocks were being returned to the pool. Tim did fix it but its one of the things I still try if a delete is taking to long. >>

EDB literally doesn't even touch the BLOBs during deletions.  It's literally about as fast as one could expect - per row, it updates the indexes, changes the row update counter, sets a flag on the row, and then it's done.

Tim Young
Elevate Software
www.elevatesoft.com