|
Posted by GregDxb on November 9, 2006, 12:24 am
Please log in for more thread options Hi Geokar,
WOw! your post knockdown every other post in this newgroup because of the
Brightest idea that Jeff ??? gave... but seriously...both ideas will give you
the same result but there are some adverse effect that JEFF wants to strongly
point out which I agree...
Now, having an orphaned records would not hamper your day to day operations
but rather if the item we have deleted have track of movement before..meaning
if you want to get a historical data then problem will come.
So, if this is a stand alone and a shop data...I don't think it will cause
you some trouble...
And for you Jeff, this a free world of interaction. If you want to prove
your point do it in a subtle manner.Not to shout !!! ( I don't know if your
the same Jeff that make a nasty comment to Roxanne , way back but I think it
is not cool).
Good Day
Note* you will not get my VOTE for MVP ( for now !) chiao :)
"Jeff @ Check Point Software" wrote:
> Geokar, GregDXB, Jocelyn, Database, and whoever else has the stupid idea to do
this,
>
> Yeah, that would delete some more of the orphaned records, now how about the
orphans here in these tables;
>
> ItemClassComponent
> InventoryOffline
> InventoryTransferLog
> Kit
> LimitEntry
> PurchaseOrderEntry
> Serial
> TransactionHoldEntry
>
> and there maybe even more cuz I didn't look very hard. ;-)
>
>
> ************************************************
>
> AGAIN, MY POINT IS THAT YOU SHOULDN'T BE DELETING ITEMS THIS WAY. DO IT
THROUGH THE RMS PROGRAM!!!!!!
>
> *************************************************
>
> --
>
> Jeff
> Check Point Software
>
> =====================================================
>
> You must be using Outlook Express or some other type of newsgroup reader to
> see and download the file attachment(s). If you are not using a reader, follow
> the link below to setup Outlook Express. Click on "Open with newsreader"
> under the MS Retail Management System on the right.
>
> http://tinyurl.com/75bgz
> =====================================================
>
> You're missing a table. You need to clear the entry for the inactive item
> from the supplierlist table as well. Otherwise you will have orphaned
> records in the supplierlist table.
>
> Backup your database first!
>
> delete from alias where itemid in (select id from item where inactive=1)
> delete from supplierlist where itemid in (select id from item where
> inactive=1)
> delete from item where inactive=1
>
> If you are in an HQ environment you will also need to delete the records out
> of the itemdynamic table as well as the 3 tables above. The ItemDynamic
> table is not present in SO databases.
> So for HQ databases:
> Backup Database!
>
> delete from alias where itemid in (select id from item where inactive=1)
> delete from supplierlist where itemid in (select id from item where
> inactive=1)
> delete from itemdynamic where itemid in (select id from item where
inactive=1)
> delete from item where inactive=1
>
>
>
>
> "GregDxb" wrote:
>
> > Good Point jeff,
> >
> > I forgot the alias table :)
> > ---delete first the alias---
> > delete from alias where itemid in (select id from item where inactive=1)
> > ---then delete the item---
> > delete from item where inactive=1
> >
> > this is just a solution of what you want.. This is just a practical
solution
> > to delete n # of records rather than going to your records n times depends
on
> > how many inactive you want to delete.
> >
> >
> > "Jeff @ Check Point Software" wrote:
> >
> > > Geokar,
> > >
> > > Again, this routine will leave orphaned records. This will only delete
the entries in the Item database, it will not delete any Supplier's numbers or
Alias' for starters.
> > >
> > > You're better off, if you _really_ want to delete them, to do it though
the program!
> > >
> > > --
> > >
> > > Jeff
> > > Check Point Software
> > >
> > > =====================================================
> > >
> > > You must be using Outlook Express or some other type of newsgroup reader
to
> > > see and download the file attachment(s). If you are not using a reader,
follow
> > > the link below to setup Outlook Express. Click on "Open with newsreader"
> > > under the MS Retail Management System on the right.
> > >
> > > http://tinyurl.com/75bgz
> > > =====================================================
> > >
> > > hi,
> > >
> > > normal procedure..before running any update or delete query, back up
your
> > > data..
> > >
> > > this is the query to delete inactive items
> > >
> > > * I assume that you have updated/set your item into inactive
> > > ---------------------------------------------
> > > delete from item where inactive=1
> > > ---------------------------------------------
> > > or if you want to delete 2000 year old data you can use this
> > > ---------------------------------------------
> > > delete from item where (item.lastupdated <= CONVERT(DATETIME,
'2001-01-01
> > > 00:00:00', 102))
> > > --------------------------------------------
> > >
> > > Run this query after backup is made successfully.
> > >
> > > Good Luck
> > >
> > > "geokar" wrote:
> > >
> > > > Great,
> > > > Now how about deleting inactive items?
> > > >
> > > > "jocelyn" wrote:
> > > >
> > > > > OK, here is your delete qty query from SO Admin:
> > > > >
> > > > > *Backup first*
> > > > >
> > > > > UPDATE Item SET Quantity = 0
> > > > >
> > > > > I would also back report this one in HQ Mgr.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "geokar" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > Can someone please give me a SQL Query make all On Hand
quantities zero and
> > > > > > to delete all Inactive items? I am opening a new store and have
copied my
> > > > > > database to the new location, but I don't think it's necessary
to keep 3 year
> > > > > > old items on file
| 
Yahoo! 
Google 
Windows Live 
del.icio.us 
digg 
Netscape 
XML Sitemap