Delete Statement

[deulyd]

Hi,

I have a table named LIMSVERSIONS which links to 5 other table with the VERSIONID field.

There is a lot of data in LIMSVERSIONS table (about 20000 rows) and I need to remove all the orphan rows in that table. I tried this way :

delete from MYVERSIONS
where VERSIONID in (
select VER.VERSIONID
from LIMSVERSIONS VER
left outer join LIMSXFDFORMS FRM ON VER.VERSIONID = FRM.VERSIONID
left outer join LIMSSERVERSCRIPTS SSC ON VER.VERSIONID = SSC.VERSIONID
left outer join LIMSDATASOURCES DS ON VER.VERSIONID = DS.VERSIONID
left outer join LIMSCLIENTSCRIPTS CSC ON VER.VERSIONID = CSC.VERSIONID
where FRM.ORIGREC IS NULL
and SSC.ORIGREC IS NULL
and DS.ORIGREC IS NULL
and CSC.ORIGREC IS NULL)

But there is too many rows ("IN" only support 1000 max) and its really slow.

Anybody have a clue?

Thanks

 

[LKBrwnDBA]

try "EXISTS"

delete from MYVERSIONS 
where EXISTS(
select 1
from LIMSVERSIONS VER 
left outer join LIMSXFDFORMS FRM ON VER.VERSIONID = FRM.VERSIONID 
left outer join LIMSSERVERSCRIPTS SSC ON VER.VERSIONID = SSC.VERSIONID 
left outer join LIMSDATASOURCES DS ON VER.VERSIONID = DS.VERSIONID 
left outer join LIMSCLIENTSCRIPTS CSC ON VER.VERSIONID = CSC.VERSIONID
where VER.VERSIONID = MYVERSIONS.VERSIONID
and FRM.ORIGREC IS NULL
and SSC.ORIGREC IS NULL
and DS.ORIGREC IS NULL
and CSC.ORIGREC IS NULL)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

I need to remove all the orphan rows in (LIMSVERSIONS) table. I tried...delete from MYVERSIONS

First, deleting rows from "MYVERSIONS" will not delete rows from "LIMSVERSIONS".


Second, once you get your data "cleaned up", I highly recommend that you enforce your referential integrity via declared foreign keys in your child tables.

Third, we can help you devise a more efficient DELETE statement if you can confirm which child table(s) contain orphaned rows that point to no-longer-existent parent rows from which parent table(s).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[deulyd]

Thanks for the lighning quick answer!

- The already tried the EXIST statement before, but seems to take FOREVER!!!

- forget about the "MYVERSIONS" table, it supposed to be "LIMSVERSIONS" (typo error, it was its previous name)

- What I exactly need is to clear data in LIMSVERSIONS where there is no Match in one of those 4 tables :
LIMSXFDFORMS.VERSIONID
LIMSSERVERSCRIPTS.VERSIONID
LIMSDATASOURCES.VERSIONID
LIMSCLIENTSCRIPTS.VERSIONID

Thanks a LOT for your help!

 

[maswien]

Not sure if following is faster (not tested)

delete from MYVERSIONS VER 
left outer join XFDFORMS FRM ON VER.VERSIONID = FRM.VERSIONID 
left outer join SERVERSCRIPTS SSC ON VER.VERSIONID = SSC.VERSIONID 
left outer join DATASOURCES DS ON VER.VERSIONID = DS.VERSIONID 
left outer join CLIENTSCRIPTS CSC ON VER.VERSIONID = CSC.VERSIONID
where FRM.ORIGREC IS NULL
 and SSC.ORIGREC IS NULL
 and DS.ORIGREC IS NULL
 and CSC.ORIGREC IS NULL

 

[SantaMufasa]

Deulyd, try this for speed:

delete from MYVERSIONS 
where EXISTS(select 1
               from (SELECT VERSIONID from LIMSVERSIONS
                     minus (SELECT VERSIONID from LIMSXFDFORMS
                            union
                            SELECT VERSIONID from  LIMSSERVERSCRIPTS
                            union
                            SELECT VERSIONID from LIMSDATASOURCES
                            union
                            SELECT VERSIONID from LIMSCLIENTSCRIPTS)
                     ) deletes
              WHERE deletes.versionid = myversions.versionid
             );

Let us know if this is fast enough.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[deulyd]

Hi guys, thanks to try but those are still too slow! :/ What I did finally is I created a vb app that gets all VERSIONID to delete into a recordset. Then I group all the VERSIONID by group of 20 in an array

Then I just run this the amount of time we got 20 versions :

delete from LIMSVERSIONS where VERSIONID in ( < 20 version Id> )

It took less than a minute to run!

Thanks