The SQL Editor is where you can edit data using SQL statements. The Visual and SQL tabs only show if an Admin enables the following permissions:

EditSQLEditorAdvanced
EditSqlEditor.

If an admin only enables EditSqlEditor, then you can only use the Visual editor.

Click the SQL tab to show the SQL Editor Query Box, where you can input SQL statements to add/change data.
Click Run to display the results.
- All queries must have the schema name ([Client] or [dbo]) or they will fail.
- Query results only display the top 300 results in the data table.
- The results in the data table are read-only, and you can't edit them like they can in the Visual editor.
- Type a SELECT statement that includes WHERE and/or JOIN, and the results will show in the data table.

Modifying Data

You can type a UPDATE/DELETE/INSERT statement to affect live data. There is no limit to how many rows you can affect. If you enter an UPDATE or DELETE statement without a WHERE clause, you'll have to confirm in the pop-up.

⚠Important

Modifying blacklisted tables or columns through the SQL data editor is not allowed.

Running DROP, ALTER, EXEC, or TRUNCATE TABLE statements are not allowed, as well.

For example:

DROP TABLE Countries
ALTER TABLE Countries ADD TestColumn int
EXEC CRM_CreatePaymentBatch
TRUNCATE TABLE Countries

Selecting Tables

Under the SQL tab, Click a table name in the Table List sidebar to populate the query text window with:

SELECT * FROM [schema].[tablename]

❗Exception

If you have a statement written in the Query Box, selecting a table from the sidebar won't update the text. However, it will populate the data table results.

Bracketing Protected Words

Brackets ([ ]) are required for keywords or special chars used in the column names or identifiers, such as GROUP and END. For example, the CRM_AssociateTypes table has a column named Group. If you want to select this column in your query, format it: [Group].

SQL Log

The system logs all changes when using this tool in the SqlEditor_AuditLog table.