Delete inactive items

I do not know your queries, but to delete all quantities (OH and negative) you should just do a full physical inventory to set the qty = 0.

SO Mgr> Inventory> Physical Inv> Generate for all items> Calculate> Commit.

Then back report in HQ Mgr with a Request Full Inv Count and a task 190.

OK, here is your delete qty query from SO Admin:

UPDATE Item SET Quantity = 0

I would also back report this one in HQ Mgr.

"geokar" wrote:

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 Great,

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 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.

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 Po> Geokar,

entries in the Item database, it will not delete any Supplier's numbers or Alias' for starters.

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:

entries in the Item database, it will not delete any Supplier's numbers or Alias' for starters.

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 Po> Geokar, GregDXB, Jocelyn, Database, and whoever else has the stupid idea to do this, >

orphans here in these tables;

THROUGH THE RMS PROGRAM!!!!!!

the entries in the Item database, it will not delete any Supplier's numbers or Alias' for starters.

the program!

quantities zero and

copied my

to keep 3 year