web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / SQL query to clear cer...
Microsoft Dynamics RMS (Archived)

SQL query to clear certain alias from alias table

(0) ShareShare
ReportReport
Posted on by Shane O' Malley 388

Hi,

We use the alias table to store barcodes and found that a recent import of new items had the wrong bar codes attached to them.

I need to run an sql query to delete all of the aliases that match a certain department and SubDescription1.

Very little experience in SQL.

Would anyone be able to point me in the right direction to design the query and send it to each store.

I have a test database that i can use to verify it works.

Thanks

Shane

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    delete alias

    where itemid in

    (select item.id from item inner join department on item.departmentid=department.id

    where subdescription1 like '%xxx%' and department.name ='zzzz')

    A Hassan

    RMS Leaders

  • Shane O' Malley Profile Picture
    Shane O' Malley 388 on at

    Thats great. Works great on the test database.

    Much appreciated.

    Shane

  • Shane O' Malley Profile Picture
    Shane O' Malley 388 on at

    Ran into a problem.

    When i ran it based on the code above it deleted all aliases that corresponded to the subdescription1 criteria but ignored department.name criteria.

    I changed the code to use department.id instead and it worked but left me with the problem that the department.id is not necessarly the same in each store. Only have 5 stores so not a major problem but would like to learn why it did not work.

    Any ideas.

    Shane

  • Community Member Profile Picture
    Community Member on at

    What is your criteria? Do u want to consider the items with a certain subdescription belonging to a certain department? or u want to consider items with a certain subscription OR items in a certain department?

    try changing the  (and department.name ='zzzz') to ( and department.name like '%zzzz%') or ( and department.code='zzzz')

  • Shane O' Malley Profile Picture
    Shane O' Malley 388 on at

    No real luck.

    Changed the query a bit so it just lists results rather than deletes for the moment.

    Now  

    USE Rmsdalkey

    select ItemID

    from Alias

    where itemid in

    (select item.ID from Item inner join department on item.DepartmentID=DepartmentID where SubDescription1 like '%SS14%'and department.Name like 'force ten')

    If i do this it lists over 10000 records IE All that match SS14

    If i use department.ID='3383'    it lists 5 (Which is correct.)

    I think this works because it is pulling the department.ID from the Item table (Same as where it is pulling the SubDescription1

  • Community Member Profile Picture
    Community Member on at

    Something's wrong with the department name u use, there shouldn't be any problem in using the name, but if it' still not working, try the department code, please note that using ( like 'force ten') is the same as ( = 'force ten'), to be able to benefit from the LIKE operator, u need to consider the '%', for example ( like '%force ten%')

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans