Edit Infor CRM (Saleslogix) data in SQL

Sometimes in Infor CRM (Saleslogix), you need to change or fix something directly in SQL. For folks experienced in SQL, this is not difficult.  But for some us, this can be a daunting task.  I am going to show you some simple SQL statements that can be used to delete or change a single piece of data in CRM.  I must emphasize, any time you are editing live SQL data, you want to make very sure you have recent backup of the database.  Used correctly, these queries should not cause any issues, but best to be prepared, just in case.  Now, to be extra careful, I am going to break this down into many steps.  Folks who are more confident in SQL could combine or eliminate some of these steps, but I want to make this easy to follow by making sure each step does exactly one thing.

First, open up SQL Server Management Studio, and connect the to the SQL instance that contains your CRM database.  Make sure you are logging in with an account that has rights to edit the database. Expand Databases, then expand your CRM database, then expand Tables.  Right click on the table you need to edit, and click Select Top 1000 Rows.  In the query screen to the right, the top half shows the SQL query used to pull the top 1000 rows, while the bottom half is the actual 1000 rows of data.  For any tables you might need to edit directly, the first column will be the ID.  (First column of Contacts is CONTACTID, first column of Account is ACCOUNTID, etc.)  Each row has its own ID, so this is the best field to use to specify exactly one row of data.

To get the ID we need we are going to alter the query in the top half of the screen.  (You can skip this if you already know the ID.)  The query you see there now is just the query selecting all the columns from the listed table. We are going to tell it to be more picky.  Put your cursor at the very end of the query start a new line, and type

WHERE columnname = ‘value’

Then click Execute.  For example (I am using the test database for these examples), if you are looking for a contact with the last name of Wright, you would type

WHERE LASTNAME = ‘Wright’

When you click Execute, the query will display all the contacts with a last name of Wright.  Now, since our goal is to edit a single piece of data, we want to locate the Wright we want (if there is more than one) and copy the CONTACTID.  We will need it for out next step.

Now to edit the data.  Select New Query, and type in your query.  To edit a single value, you would use the following:

UPDATE tablename

SET column = ‘value

WHERE idcolumn = idvalue

For example lets say you wanted to change Barry Wright’s middle name to Lee. In the previous step, we found Barry Wright, and copied his CONTACTID of CA2EK0013120.  You would type the query:

UPDATE CONTACT

SET MIDDLENAME = ‘Lee’

WHERE CONTACTID = ‘CA2EK0013120’

Remember, always include the WHERE line in your query.  If you leave it out, it will update all the values in the table, rather than just one.

If you want to get rid of value, you set it equal to NULL (no quotes).  So if you were instead wanted Barry Wright to have no middle name, you would use:

UPDATE CONTACT

SET MIDDLENAME = NULL

WHERE CONTACTID = ‘CA2EK0013120’

Finally, if you want to get rid of an entire entry, not just one value but the whole row, you use DELETE FROM instead of UPDATE.  So if you wanted to completely delete Barry Wright from the CONTACT table, you would type:

DELETE FROM CONTACTS

WHERE CONTACTID = ‘CA2EK0013120’

There is no middle line, as you don’t have to specify which values you are deleting for this contact.  You are deleting the entire contact.  As before, make sure you include the WHERE, or it will delete all the values in the table.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!