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

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SQL Query to change IT...
Microsoft Dynamics GP (Archived)

SQL Query to change ITEMTYPE in IV00101

(0) ShareShare
ReportReport
Posted on by Community Member

We have multiple Items in IV00101 that are of type 5 and 6. We need to temporarily change them to type 1 so we can run a test. I tried several different query variations, but could not come up with a single SQL query that changes all the Type 5 and 6 Items to 1. Is there a solution somewhere? I will eventually need to change them back, so I thought anything that worked could just be reversed. Any help is greatly appreciated.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    This should work:

    if OBJECT_ID('IV00101_TEMP') is not null
	drop table IV00101_TEMP;

select ITEMNMBR, ITEMTYPE into IV00101_TEMP from IV00101 where ITEMTYPE in (5,6);
update IV00101 set ITEMTYPE = 1 where ITEMTYPE IN (5,6);

-- to return to original state

update a set a.ITEMTYPE = b.ITEMTYPE
from IV00101 a inner join IV00101_TEMP b on (a.ITEMNMBR = b.ITEMNMBR);


  • Community Member Profile Picture
    Community Member on at

    Let me see if I understand this.

    The first lines create the new table, IV00101_TEMP.  The second piece copies our Item types 5 and 6 to the new TEMP table and then Updates those to Type 1 in the original.

    The return just copies the original "move" from IV00101 back? Do I have this right?

  • Verified answer
    MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    Yes sir! Once you complete your test, run the second part to restore it back to its original values. Of course, I want to make sure you understand you should test this out first in a test environment.

  • Community Member Profile Picture
    Community Member on at

    Worked like a charm in TEST. Will run our Inventory tests and revert the Item Types once complete. Thanks for the lift!

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
talty09 Profile Picture

talty09 2

#1
Anthony Beatty Profile Picture

Anthony Beatty 2

#3
CP04-islander Profile Picture

CP04-islander 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans