---
title: "SQL Experts Only: Tips & Tricks"
description: "SQL Experts Only: Tips & Tricks"
---

[![Oasis Solutions](https://blog.oasisky.com/hs-fs/hubfs/2-1.png?width=300&height=150&name=2-1.png "Oasis Solutions")](http://oasis.solutions)

Search for:

[Help Desk](https://oasisky.com/help-desk/)

Search for:

[Help Desk](https://oasisky.com/help-desk/)

# SQL Experts Only: Tips & Tricks

by [Oasis Solutions](https://blog.oasisky.com/author/oasis-solutions)  | Mar 4, 2015 | [ERP](https://blog.oasisky.com/tag/erp)  | [0 Comments ](https://blog.oasisky.com/blog/sql-experts-only-tips-tricks#comments-listing)

- [Tweet](https://twitter.com/share)

I recently assisted a customer in adding a user-defined-field (**UDF**) to the CI_Item table to add a custom ship code.  The addition of the UDF is a routine process we do frequently for customers.  What made the request unique was they wanted the open Sales Orders, Sales Order History and AR Invoice History tables to be updated with the new custom values for the line items.  Given a period of time for the customer to populate the newly created field with data using Inventory Item Maintenance function in Sage 100 Premium, we created the following scripts to update the information:

To run the queries open the SQL Management Studio and click the “New Query” button.  The provided scripts can be copied and pasted in to the new query screen or the management studio; only needing to change the user-defined to your unique field name.

**To update open sales order lines:**

*UPDATE S*

*SET S.UDF_SHIP_REGION = I.UDF_SHIP_REGION*

*FROM SO_SalesOrderDetail S inner join CI_Item I*

*ON S.ItemCode = I.ItemCode*

 

**To update open sales order history lines:**

*UPDATE S*

*SET S.UDF_SHIP_REGION = I.UDF_SHIP_REGION*

*FROM SO_SalesOrderHistoryDetail S inner join CI_Item I*

*ON S.ItemCode = I.ItemCode*

 

**To update open sales order history lines:**

*UPDATE A*

*SET A.UDF_SHIP_REGION = I.UDF_SHIP_REGION*

*FROM AR_InvoiceHistoryDetail A inner join CI_Item I*

*ON A.ItemCode = I.ItemCode*

 

**To update uposted Sales Order Invoice lines:**

*UPDATE S*

*SET S.UDF_SHIP_REGION = I.UDF_SHIP_REGION*

*FROM SO_InvoiceDetail S inner join CI_Item I*

*ON S.ItemCode = I.ItemCode*

 

Do you need EXPERT help for you Sage 100 ERP software? [Oasis Solutions Group](https://oasisky.com) can help!

 

 

### Categories

- [ERP (282)](https://blog.oasisky.com/tag/erp)
- [News (192)](https://blog.oasisky.com/tag/news)
- [Industry Insights (76)](https://blog.oasisky.com/tag/industry-insights)
- [Press Release (52)](https://blog.oasisky.com/tag/press-releases)
- [Oasis Team (48)](https://blog.oasisky.com/tag/oasis-team)
- [Our Partners (27)](https://blog.oasisky.com/tag/our-partners)
- [Best Practices (22)](https://blog.oasisky.com/tag/best-practices)

[See all](https://blog.oasisky.com/blog/sql-experts-only-tips-tricks#)

### Recent Posts

![Oasis Solutions](https://blog.oasisky.com/hubfs/Oasis_Solutions_January2019/Images/footer-rev-logo@2x.png)

#### [Connect With Us](https://oasisky.com/contact)

- Headquarters
- [Louisville, KY 40222](https://oasisky.com/louisville-kentucky-hq/)
- [Phone: 502.429.6902](tel:15024296902)

- [Lexington, KY 40504](https://oasisky.com/lexington-ky/)
- [Nashville, TN 37219](https://oasisky.com/nashville)

- [Facebook ](https://www.facebook.com/OasisSolutionsGroup)
- [Twitter ](https://twitter.com/Oasisky)
- [YouTube ](https://www.youtube.com/channel/UCPk1-r7nnH9H8_xUF0Cdb8Q)
- [LinkedIn ](https://www.linkedin.com/company/oasis-solutions-group)

#### ERP

#### Business Intelligence

#### CRM

#### Other Solutions

#### About Oasis

#### Learn

[![Oasis Solutions Group BBB Business Review](https://seal-louisville.bbb.org/seals/blue-seal-200-130-bbb-6000955.png)](http://www.bbb.org/louisville/business-reviews/computer-software-publishers-and-developers/oasis-solutions-group-in-louisville-ky-6000955/#bbbonlineclick)

© 2025 Oasis Solutions

[Help Desk](https://oasisky.com/help-desk/)

```json
{
  "@context" : "https://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Oasis Solutions",
    "url" : "https://blog.oasisky.com/author/oasis-solutions"
  },
  "dateModified" : "2019-01-24T21:06:15.747Z",
  "datePublished" : "2015-03-04T19:57:45.000Z",
  "headline" : "SQL Experts Only: Tips & Tricks",
  "mainEntityOfPage" : {
    "@id" : "https://blog.oasisky.com/blog/sql-experts-only-tips-tricks",
    "@type" : "WebPage"
  },
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "url" : "https://blog.oasisky.com/hubfs/Oasis-logo-vertical-multi-black-1.png"
    },
    "name" : "Oasis Solutions"
  }
}
```