Understanding DELETE Statements

After you are comfortable with selecting information and isolating it, I would then recommend moving on to DELETE statements.  You must be cautious when running a delete statement as this will remove data from your system.  Because you will be making a change to your data, I always recommend getting into the practice of creating a backup before running any statement that will alter your data.  You may think, I am extremely careful and this is unnecessary.  I used to think this and then accidently delete an entire table with a poor query.  The only way I was able to fix my mistake was by having a backup of the system.

Typically, I would create a select statement for the information that I want to delete and then replace SELECT * FROM table WHERE Condition with DELETE table WHERE Condition.  In Microsoft Dynamics GP I will typically use the Column Dex_Row_ID to narrow my results when I only want to remove one line.  This is because Dex_Row_ID is a unique key and does not duplicate in any of the tables.

SELECT BACHNUMB, MKDTOPST, Dex_Row_ID FROM SY00500 WHERE DEX_ROW_ID=’5′

Would then be changed into a delete statement with the following

DELETE SY00500 WHERE DEX_ROW_ID=’5′

When using a select statement and a delete statement in the same query window, it is best to also use two – in front of a statement that you do not want to run right away.  This can assist with accidently deleting the wrong row or table.

Top Tips from This Post

  1. Always make a backup before running queries that alter data.
  2. Isolate the information you want to delete with a select statement.
  3. With Microsoft Dynamics GP, the Dex_Row_ID Column is great to isolate information.
  4. Use — before delete statements to keep the statement from accidently running
  5. In Microsoft Dynamics GP it is recommended to delete records through the system if possible and only manually remove records via SQL as a last result.

Understanding the SQL Query Window

Knowing where to look to run a query is also extremely helpful to getting started.  There are three locations within SQL Server that you need to be aware of to get started.

Post 20.1.png

  1. You can select New Query and this will bring up a new window to type your SQL query.
  2. You will see a box appear under the new query with the name of one of the databases. Whatever is in this box is the database that the query will run against (except if specifies otherwise in your query).
  3. The third box is the name of the databases. If you right click on your database that you want to pull information from and select New Query.

Tips:

  • You can highlight part of a query and it will not run the entire script, only what is highlighted.
  • You can select F5 or the execute button in box 2 to run the query.
  • Adding – before a query will make it not able to run. This is useful if you have a select statement and a delete statement in same query window and don’t want the delete statement to run yet.

 

 

SQL Saturday

Learning SQL and becoming involved in the technical community can be difficult if you do not know where to look.  A fantastic event that encourages people of all ability levels is through PASS.  PASS is an independent, not-for-profit organization run by and for the community.

One event that they offer for each of their communities is SQL Saturday.  This event takes an entire Saturday for free training sessions that varies in topics.  The sessions are offered once a year per area and have a variety of locations around the work.  They can include technical sessions lead by many local speakers.  I encourage checking out the calendar to see when it is offered in your area.   Sessions are available all around the world.

If you are interested in becoming a DBA, please consider attending an event near you and supporting your community.

Understanding SELECT Statements

The first time anyone looks at SQL can be overwhelming.  Mostly because it is like learning a foreign language.  You need to learn the vocabulary and the syntax to get what you want.  The first step to learning should be how to navigate information.  In my examples, I will be using examples from Microsoft Dynamics GP 2015’s database.  I will also be using the table PM00200 (Vendor Master) for all Select queries for my examples.

The simplest concept within SQL querying is the select statements.  Once you understand the concept you would be able to apply the select statements to all types of databases.  It is important to note that a select statement does not change data in any shape or form.  Select statements are simply a method to pulls data in a way that is simpler to analyze.

The vocabulary that we will be using in this post are:

SELECT – Pulls rows and columns from the database
FROM – Specifies the specific table or view.  Always used in conjunction with a SELECT, DELETE, and UPDATE statement.
WHERE – Specifies additional conditions for a SELECT, DELETE, or UPDATE statement.
GROUP BY – Specifies which columns you want to have information grouped by.
ASC – Is used with GROUP BY to place items in an ascending order.
DESC – Is used with GROUP BY to place items in a descending order.

Let’s start with the basics.  A simple select statement.  All select statements have similar components.  If you have questions about navigating the SQL query windows please see my post Understanding SQL Query Window.

  • They all start with the word SELECT.
  • Then they specify the columns. If you want all columns in a table to appear then you would select an asterisk (*).  If you want specific columns, then you would select the column name and separate then with a comma.
  • Then you would use the word FROM to specify where the columns are located.
  • Then you will type the column name.

Using my example for this you would type the following to return all columns

SELECT * FROM PM00200

sql2-1

This returns all the columns.  Let’s say we only want the vendor ID, Vendor Name and their state.  We would then take the column names and replace the asterisk.

SELECT VENDORID, VENDNAME, STATE FROM PM00200

sql2-2

Now let’s say we only want to see the vendors for the state of Illinois.  To accomplish this we would add in the WHERE clause.

SELECT VENDORID, VENDNAME, STATE FROM PM00200 WHERE STATE=’IL’

sql2-3

Please note that the single ‘ sometimes does not copy and paste correctly into the query screen and you will need to delete and retype this into the query window.  Adding ASC or DESC to the end will change how the order goes.

sql2-4

SELECT VENDORID, VENDNAME, STATE FROM PM00200 ORDER BY STATE ASC
SELECT VENDORID, VENDNAME, STATE FROM PM00200 ORDER BY STATE DESC

ASC is the abbreviation for ascending.  DESC is for descending.  Meaning ASC will place the items in alphabetical (ascending) order and DESC will place the items in reverse alphabetical (descending) order.

Top 5 SQL Tips for Microsoft Dynamic GP

  1. Think necessity – Only look for the information you really need. The fewer rows that you pull in the quicker your query will be.
  2. Keep it simple stupid (KISS) – When you attempt to get fancy with the queries, it can have consequences. Meaning that the queries can take longer and pull unnecessary data.  Overcomplicating a query can cause unintended headaches that are avoidable.
  3. Select before a change – Select statement before you change it to a delete or update statement
  4. Measure twice, cut once when restoring a backup – Setup the restore and then go through the screens to verify that you have the correct databases, MDF and LDF selected.
  5. Always make a backup – Before you start troubleshooting an issue ALWAYS make a backup. Even if you do not anticipate making changes, make the backup.  For example, when I accidently dropped a table from the database while troubleshooting, anticipated hours of hardship were avoided by backing up the database before I started making changes.

Understanding SQL Tables 101

There are many concepts that are brushed aside as extremely basic.  This post is going to be for someone that does not know anything about SQL and may seem overly basic to anyone that already knows about databases.  This is the first post in a series and will address database structure.

After speaking with several people at a SQL Saturday that was not DBA’s, they wanted to learn the basics of SQL.  However, the session we spoke and much of the feedback was that it focused too much of the vocabulary and not enough on the structure.  Most of the frustration came from not knowing how to write the format to utilize the vocabulary.

I found that most classes and tutorials focus on the language online.  This is because SQL is a standardized language and has many different interfaces that can access the data.  Some examples of other interfaces are Cubrid, Firebird SQL, MySQL, SQL Server, etc.

First, let us discuss what a SQL database essentially is.  I will be breaking this down to the basics and if you know about databases it may seem overly simplified.  Databases are a storage of data and a way to organize data in a meaningful way.  A relational database is where data is stored between tables that relate to each other.  This starts with a database that will hold the various tables.  In each table, there are rows and columns where the data sits.  After the data is in a database, there needs to be a way to search for relevant information.  SQL, which stands for Structured Query Language, was designed as a standard format to pull data from a database.

This post is going to discuss databases, tables, columns, and rows. For the purpose of this post, I will be utilizing Microsoft Dynamic GP 2015 and SQL Server 2014.

Databases

With a basic install of Microsoft Dynamic GP with no custom databases and only Fabrikam created you will see the System Databases, DYNAMICS, and TWO.

post4-1

The system databases are the default databases that control the SQL instance you are on.  They are master, model, msdb, and tempdb.

DYNAMICS would be the overall database for Microsoft Dynamic GP.  When installed DYNAMICS can be changed to another name.  In a multi-tenant environment, you can have multiple DYNAMIC databases on an instance as long as they have different names.  For the sake of simplicity, we will not be altering the name.

TWO is the Fabrikam database that is provided by Microsoft.  This is a sample company that has sample data.  If you are new to Microsoft Dynamic GP or querying, I would recommend installing Fabrikam as a means to practice without harming your company’s accounting data.

For any install of Microsoft Dynamic GP, you will have one Dynamics databases and one or more company databases.

Tables

Along with the install of a database, tables are also installed.  As can be seen in this screenshot:

post4-2

Each table represents a data-set that is used in Microsoft Dynamic GP.   Please refer to my Table Reference page for more information on specific tables that are useful in Microsoft Dynamic GP.

Data is sorted in different tables within databases.   For example, in Microsoft Dynamic GP, one table GL00100 is the Account Master.  In this table, you will find the stored information for accounts.  Table GL00105 is the Account Index Master.  Every record in the GL00100 has a corresponding record in the GL00105 table as the Index Master is used to ensure data integrity.

Rows & Columns

Rows and columns store the actual data.  A column breaks the information down into categories.  If you have an address:

Mark White Washington Hill Ave Virginia, VA 12345

The columns would break the information apart.  With this example, there may be a firstname, lastname, address1, state, and zip.  Here is a screenshot of a column in SQL server.  The screenshot is of the batch table SY00500.

post4-3

Each row represents one record item.  The first record is highlighted in the following screenshot.

post4-4