SELECT cc.config_id, cc.type_name, cc.version_tag
FROM ambari.clusterconfig cc, ambari.clusterconfig ccm
WHERE cc.config_id NOT IN (SELECT scm.config_id FROM ambari.serviceconfigmapping scm) 
AND cc.type_name != 'cluster-env' AND cc.type_name = ccm.type_name AND cc.version_tag = 
ccm.version_tag;

CREATE TEMPORARY TABLE orphaned_configs AS
(SELECT cc.config_id FROM ambari.clusterconfig cc WHERE cc.config_id NOT IN 
(SELECT scm.config_id FROM ambari.serviceconfigmapping scm) AND cc.type_name != 
'cluster-env');

DELETE FROM ambari.clusterconfig WHERE config_id IN 
(SELECT config_id from orphaned_configs);