Hi everybody!
I have a doubt, how can i to delete records using only sql languaje into crm, and where i could put that code to work’s fine?
some like “delete from
There is not one single universal query. It depends on each of the modules. And in any case to delete one record you probably need to perform several queries (you may also manage with rather complex queries that span across multiple tables and multiple records per table).
The database is very complex and there are many related tables.
If you don’t know exactly what you are doing you will end up messing your database up. The least that you may get are orphan records that will never be accessed anywhere else.
But you may get into situations where you may have some orphan records that, when executing SuiteCRM may require data from other records that you will have deleted causing a failure of the system.
If I were you I would:
. analyse in depth the data structure of the information that you want to delete
. test your queries in a copy of the database
. thest thoroughly in a test environment that no side effetcs are created with the deletion
One more thing: SuiteCRM, normally, doesn’t delete records from the database. It only marks them by modifying the delete field they have (soft delete).
To get started I recommend that you have a look at the SugarCRM 6.5.24 database schema here: http://apidocs.sugarcrm.com/schema/6.5.24/ce/index.html
and that you analyse your database using a similar tool or phpMyAdmin. Please note that SuiteCRM has more tables compared to SugarCRM and, additionally your customisations may add both tables and additional fields.
Have a look here: https://github.com/audoxcl/SugarCRMCustomQueries
and here: http://www.sugarqueries.com/
Maybe this can help but I am not sure.
This Query deletes duplicate records
DELETE FROM your_table
WHERE id NOT IN (SELECT *
FROM (SELECT MIN(n.id)
FROM your_table n
GROUP BY n.column, n.column, n.column) x)
This query deletes records based on column data (something_here = same value)
DELETE FROM your_table
SET your_column = “something_here”
WHERE your_column = “something_here”;
Hope this helps. Post is dated, but I use these queries frequently. I have many more saved from production instance if needed.