This article is relevant if you are a NetSuite power user or administrator and you would like to update the database using SQL.

Background

Since NetSuite released the N/query Suitescript library which allows you to use NetSuite SuiteQL (SQL) to query the database, our Systems Integration firm, Prolecto Resources, has developed a number of utilities to help our consultants, and our clients’ administrators open up the power of the platform. For those that are interested in the SuiteQL framework, please consider these articles:

1. 2021: NetSuite SQL Query Tool
2. 2021: Content Renderer Engine 2.0 with NetSuite SuiteQL
3. 2021: Render NetSuite SQL Queries Like Saved Searches
4. 2021: NetSuite SQL to Assess License Usage and Login Patterns
5. 2021: Learn How To SQL Query NetSuite Multiple Select Fields
6. 2021: Learn NetSuite SQL to Lookup Project Resource Addresses

We are using SuiteQL more and more in our innovations and enhancements because it allows us to break through perceived limitations while it expands our abilty to take care of various situations in our client organizations.

More recently, Boban D., Senior Technical Analyst, continues to invent on the theme. In this article, we discuss and demonstrate how you can use SuiteQL to update the NetSuite database.

Learn How To Update NetSuite Using SuiteQL

While NetSuite does not explicitly offer a way to update the database with SQL, we enhanced our Prolecto SuiteQL Query Tool to use special syntax that will indeed update the NetSuite database. At the time of this article, it supports the following operations:

1. Touch: simply ask a target record to fire the underlying record’s edit/save logic.
2. Update: on target records, update information that is referenced by the field name.
3. Insert: create records of a particular type and update the respective referenced fields.
4. Copy: from a source record, make a copy and update any columns referenced.
5. Transform: take any NetSuite transformable record and create a child from the transformed parent record.

The tool requires a very simple syntax of how to reference column information so that when the update logic fires, it knows what operation to perform. A review of the script log allows you to watch the operation to help you confirm the expected results.

Watch SuiteQL Update the NetSuite Database (7:29)

To appreciate the power that has been unlocked, we created a demonstration video (7:29) that illustrates the tool in action. Click here to watch the video.

Get the Prolecto SuiteQL Query Tool with NetSuite Update Capability

It’s important to be very cautious with this tool! Anytime you update NetSuite with tools, you have a chance at corrupting the database. You know the saying, “With great power comes great responsibility”!  This tool, like all the algorithms that our firm produces, is available to our clients without a license charge. We suspect that over time, more and more use cases will be found to enhance our capacity to be highly productive.

If you found this article meaningful, feel free to sign up for notifications to new articles as I post them. This tool is available upon request. If you want to belong to an organization that is known for great innovation, and you assess that your leadership is underappreciated, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube