Skip to main content

Notifications

Microsoft Dynamics RMS forum

SQL Statement to change Departments

Posted on by 780

I had an employee create a new Department for one Vendor. The employee named the department the vendor name.  I want to merge these items into another existing department and then delete the vendor named department ( I know this will probably orphan some sales data, but that is okay).  Can this be done through a sql command and if so could someone who knows these commands provide it?  

Thanks,

Grier

*This post is locked for comments

  • Grier Fleischhauer Profile Picture
    Grier Fleischhauer 780 on at
    RE: SQL Statement to change Departments

    Thank you for the script. I was also able to use the same logic for category by replacing department in your scripts. Worked like a champ.

    Grier

  • Verified answer
    Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: SQL Statement to change Departments

    Shouldn't be too difficult. As always, back up before executing any update statements.

    You could definitely do this all at once with nested select statements, but I like to do updates in segments to ensure I'm not making any mistakes as I go. So, I'd run

    SELECT ID FROM Department WHERE Name LIKE '%<incorrect name>%'

    to get the incorrect department id, then

    SELECT ID FROM Department WHERE Name LIKE '%<correct department>%'

    to get the correct department id, then

    UPDATE Item SET DepartmentID = '<value from statement 2>' WHERE DepartmentID = '<value from statement 1>'

    The %'s are wildcard characters to avoid having to type the department name exactly how it appears in the database, which is a pain for some of our's, but if it's a simple name or if you have multiple departments that contain the same pattern of characters, you can just use the syntax "WHERE Name = '<name>'" instead.

    The leftover department can be deleted from within RMS. If you're in a headquarters environment, be sure to issue the appropriate worksheets to push the changes down to the stores. And I don't think this should effect sales data, which is indexed by Item id, not department id. Hope this helps, and let me know if you have any questions.

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

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

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,802 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,133 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,154

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans