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
- Always make a backup before running queries that alter data.
- Isolate the information you want to delete with a select statement.
- With Microsoft Dynamics GP, the Dex_Row_ID Column is great to isolate information.
- Use — before delete statements to keep the statement from accidently running
- 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.