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.
Advertisements