Database Cleanup utility objects

Database Cleanup utility objects
The Database Cleanup utility refers to the CLEANCONF table to determine which tables and which rows to delete when a particular object and object type are specified. The following table describes preconfigured deletion scenarios from the CLEANCONF table. You can configure your own deletion objects by adding similar rows to the CLEANCONF table.

These scenarios are examples only. To get an accurate listing of all statements, see the cleanconf rows in

WC_installdir/schema/database_type/wcs.staging.xml
WCDE_installdir/schema/database_type/wcs.staging.xml
Object Type Statements
account obsolete delete from account
where markfordelete = 1
and trdtype_id = 0
and trading_id not in (select account_id from trading)
and trading_id not in (select distinct account_id from ordpaymthd)
address obsolete delete from address
where status = 'T'
and (days(CURRENT TIMESTAMP) - days(lastcreate)) >= ?
and (address_id not in (select distinct address_id from orderitems where address_id is not null))
and (address_id not in (select distinct address_id from orders where address_id is not null))
and (address_id not in (select distinct allocaddress_id from orderitems where allocaddress_id is not null))
atp_inventory obsolete delete from receipt
where qtyonhand = 0
and qtyinkits = 0
and receipt_id not in (select distinct receipt_id from ordpickhst where receipt_id is not null)
and receipt_id not in (select distinct receipt_id from ordshiphst where receipt_id is not null)
attachment obsolete delete from attachment
where days(current timestamp) - days(timeupdated) >=? and attachment_id not in (select attachment_id from trdattach)
auction retracted delete from auction
where austatus = 'R'
and (days(CURRENT TIMESTAMP) - days(updatetime)) >= ?
auction settlement_closed delete from auction
where austatus = 'SC'
and (days(CURRENT TIMESTAMP) - days(updatetime)) >= ?
auctionlog obsolete delete from auctionlog
where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ?
autobidlog obsolete delete from autobidlog
where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ?
baseitem
obsolete
delete from baseitem 
where markfordelete = 1 
and baseitem_id not in (select baseitem_id from catentry) 
and baseitem_id not in (
    select distinct baseitem_id 
    from itemspc 
    where markfordelete = 0 
    and itemspc_id in (select distinct itemspc_id from orderitems) 
    or itemspc_id in (select distinct itemspc_id from oicomplist) 
                  or itemspc_id in (select distinct itemspc_id from versionspc
                                    where versionspc_id in (select distinct versionspc_id from receipt)) 
                  or itemspc_id in (select distinct itemspc_id from radetail) 
                  or itemspc_id in (select distinct itemspc_id from bkordalloc) 
                  or itemspc_id in (select distinct itemspc_id from invreserve) 
                  or itemspc_id in (select distinct itemspc_id from rmaitem) 
                  or itemspc_id in (select distinct itemspc_id from rmaitemcmp) 
                  or itemspc_id in (select distinct itemspc_id from catentry))
bidlog obsolete delete from bidlog
where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ?
cacheivl obsolete delete from cacheivl
where (days(CURRENT TIMESTAMP) - days(inserttime)) >= ?
calculation_code obsolete delete from calcode
where published = 2
and calcode_id not in (select distinct calcode_id from ordadjust where calcode_id is not null)
and calcode_id not in (select distinct calcode_id from stencalusg where calcode_id is not null)
and calcode_id not in (select distinct calcode_id from ordcalcd where calcode_id is not null)
and calcode_id not in (select distinct calcode_id from ordicalcd where calcode_id is not null)
catalog_group obsolete delete from catgroup
where markfordelete = 1
catentry without_orderitems delete from catentry
where markfordelete = 1
and(days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
and catentry_id not in (select distinct catentry_id from auction)
and catentry_id not in (select distinct catentry_id from orderitems where catentry_id is not null)
and catentry_id not in (select distinct catentry_id from oicomplist where catentry_id is not null)
and catentry_id not in (select distinct catentry_id from rmaitem where catentry_id is not null)
and catentry_id not in (select distinct catentry_id from offer where offer_id in (select distinct offer_id from orderitems ) )
catentry without_orderitems-iitems delete from catentry
where markfordelete = 1
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
and catentry_id not in (select distinct catentry_id from auction)
and catentry_id not in (select distinct catentry_id from orderitems)
and catentry_id not in (select distinct catentry_id from oicomplist
where catentry_id is not null)and catentry_id not in (select distinct catentry_id from iitem)
and catentry_id not in (select distinct catentry_id from rmaitem where catentry_id is not null)
and catentry_id not in (select distinct catentry_id from offer where offer_id in (select distinct offer_id from orderitems))
contract obsolete delete from trading
where markfordelete = 1
and trdtype_id = 1
and trading_id not in (select distinct trading_id from orderitems)
and trading_id not in (select distinct trading_id from rma)
and trading_id not in (select distinct trading_id from ordpaymthd)
and trading_id not in (select distinct account_id from ordpaymthd) delete from productset where markfordelete = 1
and productset_id not in (select distinct productset_id from tradeposcn where tradeposcn_id in (select distinct tradeposcn_id from offer where offer_id in (select distinct offer_id from orderitems ) )) delete from tradeposcn where markfordelete = 1
and tradeposcn_id not in (select distinct tradeposcn_id from offer where offer_id in (select distinct offer_id from orderitems))
coupon_promotion expired delete from cppmn
where days(current timestamp) - days(enddate) >=?
cpgnlog obsolete delete from cpgnlog
cpgnstats obsolete delete from cpgnstats
expected_inventory_records obsolete delete from ra
where markfordelete = 1
and ra_id not in (select distinct ra_id from receipt, radetail where receipt.radetail_id = radetail.radetail_id)
expected_inventory_record_details obsolete delete from radetail
where markfordelete = 1
and radetail_id not in (select distinct radetail_id from receipt)
experiment obsolete delete from expresult
where experiment_id in (select experiment_id from experiment
where status = 'D')"
namearg="no"
sequence="1"
daysarg="no"
delete from expstats
where experiment_id in (select experiment_id from experiment where status = 'D')" namearg="no" sequence="2" daysarg="no"delete from experiment where status = 'D'" namearg="no" sequence="3" daysarg="no"
forummsg obsolete delete from forummsg
where msgstatus = 'D' or (days(CURRENT TIMESTAMP) - days(posttime)) >= ?
fulfillment_center obsolete delete from ffmcenter
where markfordelete = 1
and ffmcenter_id not in (select distinct ffmcenter_id from radetail)
and ffmcenter_id not in (select distinct ffmcenter_id from inventory)
and ffmcenter_id not in (select distinct ffmcenter_id from rma )
and ffmcenter_id not in (select distinct ffmcenter_id from orderitems)
and ffmcenter_id not in (select distinct allocffmc_id from orderitems)
and ffmcenter_id not in (select distinct ffmcenter_id from store)
and ffmcenter_id not in (select distinct rtnffmctr_id from store)
and ffmcenter_id not in (select distinct ffmcenter_id from receipt)
and ffmcenter_id not in (select distinct ffmcenter_id from auction)
and ffmcenter_id not in (select distinct ffmcenter_id from auctionlog)
inventory_adjustments obsolete delete from invadjust
where days(CURRENT TIMESTAMP) - days(adjustmentdate) >= ?
inventory_adjustment_codes obsolete delete from invadjcode
where markfordelete = 1
and invadjcode_id not in (select distinct invadjcode_id from invadjust)
itemspecification obsolete delete from itemspc
where markfordelete = 1
and itemspc_id not in (select distinct itemspc_id from orderitems)
and itemspc_id not in (select distinct itemspc_id from oicomplist)
and itemspc_id not in (select distinct itemspc_id from versionspc
where versionspc_id in (select distinct versionspc_id from receipt))
and itemspc_id not in (select distinct itemspc_id from radetail)
and itemspc_id not in (select distinct itemspc_id from bkordalloc)
and itemspc_id not in (select distinct itemspc_id from invreserve)
and itemspc_id not in (select distinct itemspc_id from rmaitem)
and itemspc_id not in (select distinct itemspc_id from rmaitemcmp)
and itemspc_id not in (select distinct itemspc_id from catentry)
message obsolete delete from message
where message_id not in (select message_id from msgmemrel)
or (days(CURRENT TIMESTAMP) - days(posttime)) >= ?
msgmemrel obsolete delete from msgmemrel
where message_id in (select m.message_id from message ms, msgmemrel m where ms.message_id = m.message_id
and (status = 'D' or ((status = 'O' or sendstat = 'S')
and (days(CURRENT TIMESTAMP) - days(posttime)) >= ?)))
order canceled delete from orders
where status ='X'
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
order completed delete from orders
where status = 'C'
and (sysdate - ?) >= lastupdate
and orders_id not in (select orders_id from orderitems where orderitems_id in (select orderitems_id from rmaitem where orderit ems_id is not null))
and orders_id not in (select orders_id from orderitems where orderitems.status != 'C')
order deposited delete from orders
where status ='D'
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
order shipped delete from orders
where status ='S'
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
order stale_guest delete from orders
where (status ='P' or status = 'I' or status = 'W' or status = 'N')
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
and orders.member_id in (select distinct users_id from users where registertype = 'G')
and (orders_id not in (select distinct orders_id from orderitems where inventorystatus != 'NALC'
and inventorystatus is not null))
order stale_non_guest delete from orders
where (status ='P' or status = 'I' or status = 'W' or status = 'N')
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
and orders.member_id in (select distinct users_id from users where registertype != 'G')
and (orders_id not in (select distinct orders_id from orderitems where inventorystatus != 'NALC'
and inventorystatus is not null))
order quotation delete from orders
where orders_id in (select child_id from ordquotrel where reltype='markedForDelete')
organization obsolete delete from member
where member_id in (select orgentity_id from orgentity where orgentity_id = ?)
pastats obsolete delete from pastats
pcstats obsolete delete from pcstats
pestats obsolete delete from pestats
policy obsolete delete from policy
where days(current timestamp) - days(endtime) > ?
and policy_id not in (select distinct policy_id from ordpaymthd)
and policy_id not in (select distinct policy_id from rma)
productset obsolete delete from productset
where markfordelete = 1
and productset_id not in (select productset_id from tradeposcn where productset_id is not null)
productset obsolete delete from productset
where productset_id in (select productset_id from tradeposcn where productset_id is not NULL
and markfordelete = 1
and type = 'C')
rfq obsolete delete from trading
where markfordelete = 1
and trdtype_id in (2, 3, 4)
and trading_id not in (select distinct trading_id from orderitems)
and trading_id not in (select distinct trading_id from rma)
and trading_id not in (select distinct trading_id from ordpaymthd)
and trading_id not in (select distinct account_id from ordpaymthd)
rma abandoned delete from rma where status in ('PRC', 'EDT')
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
and rma_id not in (select rma_id from rmaitem where rmaitem.status in ('APP', 'MAN'))
and rma_id not in (select rma_id from rtnreceipt)
rma approved_or_partly_approved delete from rma
where status = 'PND'
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
and rma_id in (select rma_id from rmaitem where rmaitem.status in ('APP', 'MAN') )
and rma_id not in (select rma_id from rtnreceipt)
rma canceled delete from rma
where status = 'CAN'
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
rma completed delete from rma
where status = 'CLO'
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
and rma_id not in (select rma_id from rtnreceipt)
rma not_approved delete from rma
where status = 'PND'
and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
and rma_id not in (select rma_id from rmaitem where rmaitem.status in ('APP', 'MAN') )
and rma_id not in (select rma_id from rtnreceipt)
rtnreasons obsolete delete from rtnreason
where markfordelete = 1
and rtnreason_id not in (select distinct rtnreason_id from rtnrcptdsp)
and rtnreason_id not in (select distinct rtnreason_id from rmaitem)
sastats obsolete delete from sastats
staglog obsolete delete from staglog
where stgprocessed = 1
and (days(CURRENT TIMESTAMP) - days(stgstmp)) >= ?
store obsolete delete from storeent
where storeent_id = ?
and type='S'

If you are deleting a store, close the store first, before deleting it. The markfordelete flag is not supported. You must use the status flag (open/closed/suspended) first, and then use the markfordelete flag. It is the status flag that determines store access.
tradeposcn obsolete delete from tradeposcn
where markfordelete = 1
and type = 'S'
users guest delete from member
where member_id in (select users_id from users where registertype='G'
and (days(CURRENT TIMESTAMP) - days(prevlastsession)) >= ?
and (users_id not in (select member_id from orders where status != 'Q'))
and (users_id > 0)
and (users_id not in (select member_id from address
where address_id in (select address_id from orderitems
where address_id is not null
and status != 'Q') or address_id in (select allocaddress_id from orderitems
where allocaddress_id is not null
and status != 'Q')
or address_id in (select address_id from orders where address_id is not null and status !='Q'))))
user registered delete from member
where member_id in (select users_id from users where registertype= 'R'
and (days(CURRENT TIMESTAMP) - days(lastsession)) >= ?
and (users_id not in (select member_id from orders
where status != 'Q')) and (users_id > 0)
and (users_id not in (select member_id from address where address_id in (select address_id from orderitems where address_id is not null
and status != 'Q')
or address_id in (select allocaddress_id from orderitems where allocaddress_id is not null
and status != 'Q')
or address_id in (select address_id from orders where address_id is not null
and status !='Q'))))
usrtraffic obsolete delete from usrtraffic
where (days(CURRENT TIMESTAMP) - days(stmp)) >= ?
vendor obsolete delete from vendor
where markfordelete = 1
and vendor_id not in (select distinct vendor_id from ra)
and vendor_id not in (select distinct vendor_id from receipt where vendor_id is not null)

Object	Type	Statements
account	obsolete	delete from account where markfordelete = 1 and trdtype_id = 0 and trading_id not in (select account_id from trading) and trading_id not in (select distinct account_id from ordpaymthd)
address	obsolete	delete from address where status = 'T' and (days(CURRENT TIMESTAMP) - days(lastcreate)) >= ? and (address_id not in (select distinct address_id from orderitems where address_id is not null)) and (address_id not in (select distinct address_id from orders where address_id is not null)) and (address_id not in (select distinct allocaddress_id from orderitems where allocaddress_id is not null))
atp_inventory	obsolete	delete from receipt where qtyonhand = 0 and qtyinkits = 0 and receipt_id not in (select distinct receipt_id from ordpickhst where receipt_id is not null) and receipt_id not in (select distinct receipt_id from ordshiphst where receipt_id is not null)
attachment	obsolete	delete from attachment where days(current timestamp) - days(timeupdated) >=? and attachment_id not in (select attachment_id from trdattach)
auction	retracted	delete from auction where austatus = 'R' and (days(CURRENT TIMESTAMP) - days(updatetime)) >= ?
auction	settlement_closed	delete from auction where austatus = 'SC' and (days(CURRENT TIMESTAMP) - days(updatetime)) >= ?
auctionlog	obsolete	delete from auctionlog where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ?
autobidlog	obsolete	delete from autobidlog where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ?
baseitem	obsolete	delete from baseitem where markfordelete = 1 and baseitem_id not in (select baseitem_id from catentry) and baseitem_id not in ( select distinct baseitem_id from itemspc where markfordelete = 0 and itemspc_id in (select distinct itemspc_id from orderitems) or itemspc_id in (select distinct itemspc_id from oicomplist) or itemspc_id in (select distinct itemspc_id from versionspc where versionspc_id in (select distinct versionspc_id from receipt)) or itemspc_id in (select distinct itemspc_id from radetail) or itemspc_id in (select distinct itemspc_id from bkordalloc) or itemspc_id in (select distinct itemspc_id from invreserve) or itemspc_id in (select distinct itemspc_id from rmaitem) or itemspc_id in (select distinct itemspc_id from rmaitemcmp) or itemspc_id in (select distinct itemspc_id from catentry))
bidlog	obsolete	delete from bidlog where (days(CURRENT TIMESTAMP) - days(actiontime)) >= ?
cacheivl	obsolete	delete from cacheivl where (days(CURRENT TIMESTAMP) - days(inserttime)) >= ?
calculation_code	obsolete	delete from calcode where published = 2 and calcode_id not in (select distinct calcode_id from ordadjust where calcode_id is not null) and calcode_id not in (select distinct calcode_id from stencalusg where calcode_id is not null) and calcode_id not in (select distinct calcode_id from ordcalcd where calcode_id is not null) and calcode_id not in (select distinct calcode_id from ordicalcd where calcode_id is not null)
catalog_group	obsolete	delete from catgroup where markfordelete = 1
catentry	without_orderitems	delete from catentry where markfordelete = 1 and(days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? and catentry_id not in (select distinct catentry_id from auction) and catentry_id not in (select distinct catentry_id from orderitems where catentry_id is not null) and catentry_id not in (select distinct catentry_id from oicomplist where catentry_id is not null) and catentry_id not in (select distinct catentry_id from rmaitem where catentry_id is not null) and catentry_id not in (select distinct catentry_id from offer where offer_id in (select distinct offer_id from orderitems ) )
catentry	without_orderitems-iitems	delete from catentry where markfordelete = 1 and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? and catentry_id not in (select distinct catentry_id from auction) and catentry_id not in (select distinct catentry_id from orderitems) and catentry_id not in (select distinct catentry_id from oicomplist where catentry_id is not null)and catentry_id not in (select distinct catentry_id from iitem) and catentry_id not in (select distinct catentry_id from rmaitem where catentry_id is not null) and catentry_id not in (select distinct catentry_id from offer where offer_id in (select distinct offer_id from orderitems))
contract	obsolete	delete from trading where markfordelete = 1 and trdtype_id = 1 and trading_id not in (select distinct trading_id from orderitems) and trading_id not in (select distinct trading_id from rma) and trading_id not in (select distinct trading_id from ordpaymthd) and trading_id not in (select distinct account_id from ordpaymthd) delete from productset where markfordelete = 1 and productset_id not in (select distinct productset_id from tradeposcn where tradeposcn_id in (select distinct tradeposcn_id from offer where offer_id in (select distinct offer_id from orderitems ) )) delete from tradeposcn where markfordelete = 1 and tradeposcn_id not in (select distinct tradeposcn_id from offer where offer_id in (select distinct offer_id from orderitems))
coupon_promotion	expired	delete from cppmn where days(current timestamp) - days(enddate) >=?
cpgnlog	obsolete	delete from cpgnlog
cpgnstats	obsolete	delete from cpgnstats
expected_inventory_records	obsolete	delete from ra where markfordelete = 1 and ra_id not in (select distinct ra_id from receipt, radetail where receipt.radetail_id = radetail.radetail_id)
expected_inventory_record_details	obsolete	delete from radetail where markfordelete = 1 and radetail_id not in (select distinct radetail_id from receipt)
experiment	obsolete	delete from expresult where experiment_id in (select experiment_id from experiment where status = 'D')" namearg="no" sequence="1" daysarg="no" delete from expstats where experiment_id in (select experiment_id from experiment where status = 'D')" namearg="no" sequence="2" daysarg="no"delete from experiment where status = 'D'" namearg="no" sequence="3" daysarg="no"
forummsg	obsolete	delete from forummsg where msgstatus = 'D' or (days(CURRENT TIMESTAMP) - days(posttime)) >= ?
fulfillment_center	obsolete	delete from ffmcenter where markfordelete = 1 and ffmcenter_id not in (select distinct ffmcenter_id from radetail) and ffmcenter_id not in (select distinct ffmcenter_id from inventory) and ffmcenter_id not in (select distinct ffmcenter_id from rma ) and ffmcenter_id not in (select distinct ffmcenter_id from orderitems) and ffmcenter_id not in (select distinct allocffmc_id from orderitems) and ffmcenter_id not in (select distinct ffmcenter_id from store) and ffmcenter_id not in (select distinct rtnffmctr_id from store) and ffmcenter_id not in (select distinct ffmcenter_id from receipt) and ffmcenter_id not in (select distinct ffmcenter_id from auction) and ffmcenter_id not in (select distinct ffmcenter_id from auctionlog)
inventory_adjustments	obsolete	delete from invadjust where days(CURRENT TIMESTAMP) - days(adjustmentdate) >= ?
inventory_adjustment_codes	obsolete	delete from invadjcode where markfordelete = 1 and invadjcode_id not in (select distinct invadjcode_id from invadjust)
itemspecification	obsolete	delete from itemspc where markfordelete = 1 and itemspc_id not in (select distinct itemspc_id from orderitems) and itemspc_id not in (select distinct itemspc_id from oicomplist) and itemspc_id not in (select distinct itemspc_id from versionspc where versionspc_id in (select distinct versionspc_id from receipt)) and itemspc_id not in (select distinct itemspc_id from radetail) and itemspc_id not in (select distinct itemspc_id from bkordalloc) and itemspc_id not in (select distinct itemspc_id from invreserve) and itemspc_id not in (select distinct itemspc_id from rmaitem) and itemspc_id not in (select distinct itemspc_id from rmaitemcmp) and itemspc_id not in (select distinct itemspc_id from catentry)
message	obsolete	delete from message where message_id not in (select message_id from msgmemrel) or (days(CURRENT TIMESTAMP) - days(posttime)) >= ?
msgmemrel	obsolete	delete from msgmemrel where message_id in (select m.message_id from message ms, msgmemrel m where ms.message_id = m.message_id and (status = 'D' or ((status = 'O' or sendstat = 'S') and (days(CURRENT TIMESTAMP) - days(posttime)) >= ?)))
order	canceled	delete from orders where status ='X' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
order	completed	delete from orders where status = 'C' and (sysdate - ?) >= lastupdate and orders_id not in (select orders_id from orderitems where orderitems_id in (select orderitems_id from rmaitem where orderit ems_id is not null)) and orders_id not in (select orders_id from orderitems where orderitems.status != 'C')
order	deposited	delete from orders where status ='D' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
order	shipped	delete from orders where status ='S' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
order	stale_guest	delete from orders where (status ='P' or status = 'I' or status = 'W' or status = 'N') and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? and orders.member_id in (select distinct users_id from users where registertype = 'G') and (orders_id not in (select distinct orders_id from orderitems where inventorystatus != 'NALC' and inventorystatus is not null))
order	stale_non_guest	delete from orders where (status ='P' or status = 'I' or status = 'W' or status = 'N') and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? and orders.member_id in (select distinct users_id from users where registertype != 'G') and (orders_id not in (select distinct orders_id from orderitems where inventorystatus != 'NALC' and inventorystatus is not null))
order	quotation	delete from orders where orders_id in (select child_id from ordquotrel where reltype='markedForDelete')
organization	obsolete	delete from member where member_id in (select orgentity_id from orgentity where orgentity_id = ?)
pastats	obsolete	delete from pastats
pcstats	obsolete	delete from pcstats
pestats	obsolete	delete from pestats
policy	obsolete	delete from policy where days(current timestamp) - days(endtime) > ? and policy_id not in (select distinct policy_id from ordpaymthd) and policy_id not in (select distinct policy_id from rma)
productset	obsolete	delete from productset where markfordelete = 1 and productset_id not in (select productset_id from tradeposcn where productset_id is not null)
productset	obsolete	delete from productset where productset_id in (select productset_id from tradeposcn where productset_id is not NULL and markfordelete = 1 and type = 'C')
rfq	obsolete	delete from trading where markfordelete = 1 and trdtype_id in (2, 3, 4) and trading_id not in (select distinct trading_id from orderitems) and trading_id not in (select distinct trading_id from rma) and trading_id not in (select distinct trading_id from ordpaymthd) and trading_id not in (select distinct account_id from ordpaymthd)
rma	abandoned	delete from rma where status in ('PRC', 'EDT') and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? and rma_id not in (select rma_id from rmaitem where rmaitem.status in ('APP', 'MAN')) and rma_id not in (select rma_id from rtnreceipt)
rma	approved_or_partly_approved	delete from rma where status = 'PND' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? and rma_id in (select rma_id from rmaitem where rmaitem.status in ('APP', 'MAN') ) and rma_id not in (select rma_id from rtnreceipt)
rma	canceled	delete from rma where status = 'CAN' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ?
rma	completed	delete from rma where status = 'CLO' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? and rma_id not in (select rma_id from rtnreceipt)
rma	not_approved	delete from rma where status = 'PND' and (days(CURRENT TIMESTAMP) - days(lastupdate)) >= ? and rma_id not in (select rma_id from rmaitem where rmaitem.status in ('APP', 'MAN') ) and rma_id not in (select rma_id from rtnreceipt)
rtnreasons	obsolete	delete from rtnreason where markfordelete = 1 and rtnreason_id not in (select distinct rtnreason_id from rtnrcptdsp) and rtnreason_id not in (select distinct rtnreason_id from rmaitem)
sastats	obsolete	delete from sastats
staglog	obsolete	delete from staglog where stgprocessed = 1 and (days(CURRENT TIMESTAMP) - days(stgstmp)) >= ?
store	obsolete	delete from storeent where storeent_id = ? and type='S' If you are deleting a store, close the store first, before deleting it. The markfordelete flag is not supported. You must use the status flag (open/closed/suspended) first, and then use the markfordelete flag. It is the status flag that determines store access.
tradeposcn	obsolete	delete from tradeposcn where markfordelete = 1 and type = 'S'
users	guest	delete from member where member_id in (select users_id from users where registertype='G' and (days(CURRENT TIMESTAMP) - days(prevlastsession)) >= ? and (users_id not in (select member_id from orders where status != 'Q')) and (users_id > 0) and (users_id not in (select member_id from address where address_id in (select address_id from orderitems where address_id is not null and status != 'Q') or address_id in (select allocaddress_id from orderitems where allocaddress_id is not null and status != 'Q') or address_id in (select address_id from orders where address_id is not null and status !='Q'))))
user	registered	delete from member where member_id in (select users_id from users where registertype= 'R' and (days(CURRENT TIMESTAMP) - days(lastsession)) >= ? and (users_id not in (select member_id from orders where status != 'Q')) and (users_id > 0) and (users_id not in (select member_id from address where address_id in (select address_id from orderitems where address_id is not null and status != 'Q') or address_id in (select allocaddress_id from orderitems where allocaddress_id is not null and status != 'Q') or address_id in (select address_id from orders where address_id is not null and status !='Q'))))
usrtraffic	obsolete	delete from usrtraffic where (days(CURRENT TIMESTAMP) - days(stmp)) >= ?
vendor	obsolete	delete from vendor where markfordelete = 1 and vendor_id not in (select distinct vendor_id from ra) and vendor_id not in (select distinct vendor_id from receipt where vendor_id is not null)

Related Concepts

Database Cleanup utility
Cleaning the database
Examples: Deleting objects
Add a configuration to the Database Cleanup utility