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 Statement to chang...
Microsoft Dynamics RMS (Archived)

SQL Statement to change Departments

(0) ShareShare
ReportReport
Posted on by Grier Fleischhauer 782

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

I have the same question (0)
  • Verified answer
    Spencer McCandless Profile Picture
    Spencer McCandless 2,087 on at

    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.

  • Grier Fleischhauer Profile Picture
    Grier Fleischhauer 782 on at

    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

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