Skip to main content

Notifications

Microsoft Dynamics RMS (Archived)

SQL query to clear certain alias from alias table

Posted on by 380

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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL query to clear certain alias from alias table

    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%')

  • Shane O' Malley Profile Picture
    Shane O' Malley 380 on at
    RE: SQL query to clear certain alias from alias table

    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 Microsoft Employee on at
    RE: SQL query to clear certain alias from alias table

    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 380 on at
    RE: SQL query to clear certain alias from alias table

    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

  • Shane O' Malley Profile Picture
    Shane O' Malley 380 on at
    RE: SQL query to clear certain alias from alias table

    Thats great. Works great on the test database.

    Much appreciated.

    Shane

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL query to clear certain alias from alias table

    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

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,902 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,336 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans