query to delete all items in a department

i imported thousands of items using RR import utility that had wrong look up 
codes. 
i can tell becuase those items are never sold.   i can re imort all those 
items and put them in a same department or supplier called delete.   how can 
i delete all the items in a department or supplier.   i dont want to mess up 
my db.    please help
0
Manoj1 (107)
12/31/2007 1:30:00 AM
pos 14173 articles. 0 followers. Follow

4 Replies
349 Views

Similar Articles

[PageSpeed] 54

hi Manos, before you do so backup your database

Then go to SO Admin | File Connect the Db | Then go to Query and click New: 
run the below query one by one

delete from supplierlist
from department,item
where item.departmentid=department.id
and   item.id=supplierlist.itemid
and department.code='xx'

delete from alias
from department,item
where item.departmentid=department.id
and   item.id=alias.itemid
and department.code='xx'

delete from item
from department
where item.departmentid=department.id
and department.code='xx'


onces work rate me,or if need more help feel free to ask.

Akber





"manoj" wrote:

> i imported thousands of items using RR import utility that had wrong look up 
> codes. 
> i can tell becuase those items are never sold.   i can re imort all those 
> items and put them in a same department or supplier called delete.   how can 
> i delete all the items in a department or supplier.   i dont want to mess up 
> my db.    please help
0
AkberAlwani (916)
12/31/2007 5:20:00 AM
Hello Akber,   Thanks for the replay.    just to make sure that this if if i 
want to delete all the items in a department called "delete".   do i need to 
change anything in the query and do i really need to run all 3 querys?   
please let me know   Thanks  jay (manoj)

"Akber Alwani" wrote:

> hi Manos, before you do so backup your database
> 
> Then go to SO Admin | File Connect the Db | Then go to Query and click New: 
> run the below query one by one
> 
> delete from supplierlist
> from department,item
> where item.departmentid=department.id
> and   item.id=supplierlist.itemid
> and department.code='xx'
> 
> delete from alias
> from department,item
> where item.departmentid=department.id
> and   item.id=alias.itemid
> and department.code='xx'
> 
> delete from item
> from department
> where item.departmentid=department.id
> and department.code='xx'
> 
> 
> onces work rate me,or if need more help feel free to ask.
> 
> Akber
> 
> 
> 
> 
> 
> "manoj" wrote:
> 
> > i imported thousands of items using RR import utility that had wrong look up 
> > codes. 
> > i can tell becuase those items are never sold.   i can re imort all those 
> > items and put them in a same department or supplier called delete.   how can 
> > i delete all the items in a department or supplier.   i dont want to mess up 
> > my db.    please help
0
Manoj1 (107)
12/31/2007 5:37:01 AM
hi manoj

yes instead of department.code='xx' you can do department.code='delete'  and 
for all 3 queries.

Akber Rate this.
"manoj" wrote:

> Hello Akber,   Thanks for the replay.    just to make sure that this if if i 
> want to delete all the items in a department called "delete".   do i need to 
> change anything in the query and do i really need to run all 3 querys?   
> please let me know   Thanks  jay (manoj)
> 
> "Akber Alwani" wrote:
> 
> > hi Manos, before you do so backup your database
> > 
> > Then go to SO Admin | File Connect the Db | Then go to Query and click New: 
> > run the below query one by one
> > 
> > delete from supplierlist
> > from department,item
> > where item.departmentid=department.id
> > and   item.id=supplierlist.itemid
> > and department.code='xx'
> > 
> > delete from alias
> > from department,item
> > where item.departmentid=department.id
> > and   item.id=alias.itemid
> > and department.code='xx'
> > 
> > delete from item
> > from department
> > where item.departmentid=department.id
> > and department.code='xx'
> > 
> > 
> > onces work rate me,or if need more help feel free to ask.
> > 
> > Akber
> > 
> > 
> > 
> > 
> > 
> > "manoj" wrote:
> > 
> > > i imported thousands of items using RR import utility that had wrong look up 
> > > codes. 
> > > i can tell becuase those items are never sold.   i can re imort all those 
> > > items and put them in a same department or supplier called delete.   how can 
> > > i delete all the items in a department or supplier.   i dont want to mess up 
> > > my db.    please help
0
AkberAlwani (916)
12/31/2007 1:14:00 PM
Hello Akber,  Thank you.    it works    
Jay

"Akber Alwani" wrote:

> hi manoj
> 
> yes instead of department.code='xx' you can do department.code='delete'  and 
> for all 3 queries.
> 
> Akber Rate this.
> "manoj" wrote:
> 
> > Hello Akber,   Thanks for the replay.    just to make sure that this if if i 
> > want to delete all the items in a department called "delete".   do i need to 
> > change anything in the query and do i really need to run all 3 querys?   
> > please let me know   Thanks  jay (manoj)
> > 
> > "Akber Alwani" wrote:
> > 
> > > hi Manos, before you do so backup your database
> > > 
> > > Then go to SO Admin | File Connect the Db | Then go to Query and click New: 
> > > run the below query one by one
> > > 
> > > delete from supplierlist
> > > from department,item
> > > where item.departmentid=department.id
> > > and   item.id=supplierlist.itemid
> > > and department.code='xx'
> > > 
> > > delete from alias
> > > from department,item
> > > where item.departmentid=department.id
> > > and   item.id=alias.itemid
> > > and department.code='xx'
> > > 
> > > delete from item
> > > from department
> > > where item.departmentid=department.id
> > > and department.code='xx'
> > > 
> > > 
> > > onces work rate me,or if need more help feel free to ask.
> > > 
> > > Akber
> > > 
> > > 
> > > 
> > > 
> > > 
> > > "manoj" wrote:
> > > 
> > > > i imported thousands of items using RR import utility that had wrong look up 
> > > > codes. 
> > > > i can tell becuase those items are never sold.   i can re imort all those 
> > > > items and put them in a same department or supplier called delete.   how can 
> > > > i delete all the items in a department or supplier.   i dont want to mess up 
> > > > my db.    please help
0
Manoj1 (107)
12/31/2007 3:06:02 PM
Reply:

Similar Artilces:

Round up a decimal in a query field
Hopefully this is an easy question. How do you round up a number in a query field? Excel has the ROUNDUP function, but I can't find anything similar in Access. Can anyone help? Function:round([tablename!fieldname,2]) I think. It may be round([tablesname!fieldname],2) "bigomega73" wrote: > Hopefully this is an easy question. How do you round up a number in a query > field? Excel has the ROUNDUP function, but I can't find anything similar in > Access. Can anyone help? Thanks Golfinray, but that only rounds to the nearest decimal place. What I want is the numb...

Help with SQL Query 06-30-10
We have a distinct list of email addresses in alpha order and we need to transform it from a single column into a grid of three columns maintaining the alpha order. The list is contained in a temp table inside of our query. We then use the temp table list and perform case statement with a mod on the row_number in a select statement to columnze the data. However, the columnar data contains a null value in two of the three columns and we are needing to remove the nulls and have the actual values on each row in the output. Here is our current sql: CREATE TABLE #tmpTable ( Email_Add...

Using Mail Merge with Criteria in a Access query
Hi; In access 2000-2003 it was possible to use the Mail Merge function in Word to access a query that has criteria required for input. You can select the Word document and then the database is opened and the criteria box is opened for input (Enter Parameter Value). This then produces the data for the letters. It used a DDE connection between Word and Access. In Office 2007 the mail merge help document says to go to the Access database and select the query and lists how to link to an existing or new letter. The DDE connection is listed but does not attach to the query? It alway...

How do I run a SQL query against a MS SQL Server database from exc
How do I run a SQL query against a MS SQL Server database from within Excel 2002? I know it can be done somehow using the SQL.Request addin function. However my attempts using this function have continually failed. Can some one please provide a working example to the pubs or Northwind dbs that I can work with and use to help debug my attempt? Thank you! ...

MS Access & Citrix
Hi, all. We have a home-grown Access application that approximately 50 users use to access different, but related, databases on different Linux servers across the US. We are trying to combine all these different databases into one database that everyone can access through a Citrix server. In the initial (very) testing phase, we have found some strange problems with our Access app that may be related to the programming within the application or may be related to the Citrix side of things (although when we try to do an analagous task going just through Access itself we don't have the same...

Can't delete a particular public folder
I have a public folder (now named "Bad Folder") on my Exchange 2003 server that I can't delete. When I try to delete it, while I am logged in as "admin" on the Windows 2003 Server that the Exchange 2003 Server runs on, I get prompted for my username and password. No matter what I enter I just get re-prompted several times, followed by "The operation failed. ID 80004005. Exchange System Manager". I have check, re-checked, and changed the permissions (both "Client Permissions" and "Administrative Rights") from "anyone can do anyth...

Cannot Delete A Public Folder
Folks, For some reason I have run into a weird problem on a folder within our Public Folders. This folder is no longer in use and must be deleted, however, when I right click it in Outlook the option to delete is greyed out. The properties page is also screwed up because I cannot add users or permissions to it. I believe the root of my problem is because the one and only defined user for this particular left the company and his AD account was deleted. All I see as a permitted user/author is the SID string of numbers. Is there a way to delete this folder manually? I've look...

deleting CRM users??
Is there a way to delete the users from the users folder? It doesn't seem like you can get rid of them or change the groups that they belong to if you make a mistake, etc. You can't delete CRM users, you just have to disable them. "wlandymore" wrote: > Is there a way to delete the users from the users folder? It doesn't seem > like you can get rid of them or change the groups that they belong to if you > make a mistake, etc. Hi, It is possible to hard delete a users in the ms crm database, but of course this is not supported. If you chose to delete this...

Outlook does not refresh imap inbox when message is deleted on ser
I have Outlook set up to use imap on two computers as well as my Palm. The problem is that messages deleted from the server will not dissappear in Outlook as long as Outlook is running. If if restart Outlook the imap inbox is updated correctly. I thougt Microsoft had solved this problem between Outlook 2000 and 2003, but obviously not. Is there a solution? You will need to manually purge the items using Edit->Purge. (I think it is on the Edit menu - I despise IMAP so I don't have a current setup to check.) --� Milly Staples [MVP - Outlook] Post all replies to the group to keep ...

delete data #2
Hello, we have CRM v3 installed in a test evironment. we have added test data for customers, products. we have created oppurtunities to invoices. we would like to delete all of this data. is there a way to do it without uninsalling and reinstalling the software? cheers, Michael. Try to delete via the UI from Advanced Find. For some of the object (Order, Invoices, Quote, etc.), depending on status some of these records cannot be deleted. Frank Lee, Microsoft CRM MVP http://microsoft-crm.spaces.live.com http://www.workopia.com/Links.htmTry "Michael P. Lindekugel" wrote:...

Count of Unique Items?
Hi, I have a worksheet (Sheet1) where column A consists of dates (in date order). I need to look at this column from another sheet (Sheet2) in the same workbook and return the number of unique dates in this column. For example, on Sheet1 cell A1 has the heading Date, A2 is 10/01/04, A3 is 10/01/04, A4 is 10/01/04 and A5 is 10/05/04. From cell A1 on Sheet2 I need to look at the range A2:A5 on Sheet1 and return the number 2 (the number of unique dates in the range). Any help would be greatly appreciated. Monte Sliger Try the following... =SUM(IF(Sheet1!A2:A5<>"",1/COUN...

SQL to update Item.LastCounted based on PhysicalInventory.CloseTime
Because of a bug/deficiency/oversight in a third party inventory counting application , I did a bunch of physical inventories that did not update the Item.LastCounted field. I need this field updated so I can see the correct date each item was counted last. I am willing to do a seperate SQL statement for each physical inventory sheet for which I have the problem. For example, I want to take each item in the PhysicalInventoryEntry table with PhysicalInventory.Code = '0000057' (or whatever the code is) and set Item.LastCounted = PhysicalInventory.CloseTime. I am not great at SQ...

Exmerge lost my calendar items
When I export from Recovery Storage Group, all of my calendar items are gone. If I merge or copy back to original store, they are there. Anyone have any ideas as to why they disappear during an export to .pst? Thanks for your help, Jeff. ...

Any idea how to delete a custom portfolio view???
Anytime I select a custom portfolio view within Money 06 Deluxe Trial, it crashes Money and I have to reinstall (and not select that particular view) in order to use the Portfolio Manager function w/in Money. Money '05 Deluxe did the same thing, hence my "upgrade" to '06 Deluxe Trial. In what I thought was more than a strange coincidence, the same thing happened when I used Money online through MSN. It crashed Internet Explorer and now if I select Portfolio online, Internet Explorer must close. Any potential solutions other than deleting the view are most welcome. Thanks...

SQL Query
I'm trying to create a query that will result in receipt transaction details from POP10310 combined with GL transactions from GL20000 and so far no success. I assume I also need POP10300 in the mix. Which field(s) should I be joining between GL20000 and POP10300 or PO10310? Thanks. Frank Hamelly, MCP-GP NOVA Solutions LLC Melbourne, FL Try using POP30310 instead. The receipt transactions would have to be posted before anything got to GL20000. -- Jim@TurboChef "Frank Hamelly, MCP-GP" wrote: > I'm trying to create a query that will result in receipt transaction &...

Inactive Items 12-17-09
I ran the Inventory Wizard 2 days ago and used the 210 worksheet to make some 4,000 items inactive. I selected "Enter items manually" and imported them from a .csv file. The file had 4 columns: The item number, description, department and the 4th column had the work "Inactive." The process worked properly in HQ but did not propogate to the stores. I am running version 2, sp2. Did you create a worksheet to send this to the stores? Afshin Alikhani - afshin@rrdisti.com Retail Realm Distribution = = = = = = = = "Rick@ASP" <RickASP@discussion...

How do I delete outbox e-mails that aren't sent.
I can not remove an e-mail that did not successfully send. It slows all my outgoing e-mails. Any ideas would be great. http://www.howto-outlook.com/faq/messagestuckinoutbox.htm -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ "jwinn" wrote: > I can not remove an e-mail that did not successfully send. It slows all my > outgoing e-mails. Any ideas would be great. ...

Query-based distribution group anomaly
When I do a TOOLS ~ OPTIONS ~ DELEGATES in Outlook 2003 and try to set a query-based distribution group as a delegate it seems to accept the group and whatever permissions I assign. But as soon as I click OK and then come back in to inspect it, the query-based distribution group has no permissions at all. This happens every time. However, in the same situation, whenever I assign a Global Security Group as a delegate and assign permissions, they stick. Should my query-based distribution group be accepted or is this kind of group not permitted as a delegate? But then if it's not perm...

Delete First storage group
I am running Exchange 2003 server with windows 2000 server. I created a new Storage group with a new Mailbox store in Exchange system manager. I then moved all available mailboxes to the new storage group successfully. I would like to delete the First storage group along with the priv.edb and priv.stm database files but I am unable to do so because the system attendant and SMTP mailboxes are still in the storage group. How can I move or delete these mailboxes to I can delete the Storage group and database files successfully? Thank you, Rob ...

Table name length in microsoft query
Hi! Is it true that the maximum length of a tablename is still 8 characters. I'm useing Excel 97 and trying to get data from a Paradox 9 table via odbc-link and Microsoft Query. I have heard a few years ago that long names are OK and supported all over the microsoftian regime. Is there any trick to avoid this without shortening all the names. Greetings from Helsinki HK ...

Query by Form Problem 06-19-07
I'm using QBF with about six different combo boxes using: [forms]! [formmain]! [combo1] or [forms]![formmain1]![combo1] -like in a VBA book. This is so users can select criteria on a form with the combo boxes, and when they are done, they hit the search button, and it opens up another form based on the query just performed by the combo selection. This worked for about three combo boxes, but when I added another one, it freezes up and opens up a blank page. It's supposed to open the new form based on the query. Is there a better way to do this? I've looked exhaustive...

Random List from Query
For accrediation purposes we have to audit a random 10% of our cases each quarter I was wondering if there is a way to run a query or report that would let me show a random 10% of cases for a specific provider. ...

List of items with commissions
Is there a report that will list items that have a commission set on them? I want to be able to email our sales reps a list, but haven't found a good way to do it. The report I'd like, but don't know how to create would have department, category, item, price, and commission amount. Thanks for any help, -CP Hi there, Retail Analytics from Professional Advantage will enable you to create that report. Basically anything that is captured in RMS can be reported on in Retail Analytics. If you would like to take a look at the product, a quick movie is available at http://www.p...

Junk E-mail folder
Is it possible to configure the Junk E-mail folder to automatically and permanently delete items older than 30 days? Thanks. You can use the AutoArchiving to do that. Right click on the folder, choose Properties and go to the AutoArchive tab "Jaycee" <jaycee131973@hotmail.com> wrote in message news:%232IGo%238NGHA.2300@TK2MSFTNGP15.phx.gbl... > Is it possible to configure the Junk E-mail folder to automatically and > permanently delete items older than 30 days? ...

Sent Email is deleted
After I sent my email they get deleted. I use to see them inside the sent item directory but know I they get deleted after they are sent . Any thing I should change in order to keet the email I sent Thank you for help Go to Tools, then Options. Under the Preferences Tab, click on Email Options. Make sure the 4th check box is checked. "msw" <moataz@warshanna.com> wrote in message news:e58pFrOyDHA.604@tk2msftngp13.phx.gbl... > After I sent my email they get deleted. I use to see them inside the sent > item directory but know I they get deleted after they are sent . ...