SQL query to delete unsued templates

[bigbangtech]

Does anyone have a short SQL query that would find and delete any product templates that are not associated with a product?

We imported a store with 1000's of items, deleted a great deal of the items and are now stuck with 100's of templates that we need to wade through before we get to the correct one.

[nickc]

Code: Select all

SELECT * FROM ac_ProductTemplates
WHERE ProductTemplateId NOT IN
( SELECT DISTINCT ProductTemplateId FROM ac_InputFields where InputFieldId IN
  ( SELECT DISTINCT InputFieldId FROM ac_ProductTemplateFields )
)

Change the initial "SELECT * FROM" to "DELETE" after vetting the results of the select.
All after backing up, of course

[bigbangtech]

Thanks Nick, that worked like a charm!

I do have a lot of rows still left in ac_ProductTemplateFields that aren't connected to anything though...

Code: Select all

SELECT * FROM ac_ProductTemplates
WHERE ProductTemplateId NOT IN
( SELECT DISTINCT ProductTemplateId FROM ac_InputFields where InputFieldId IN
  ( SELECT DISTINCT InputFieldId FROM ac_ProductTemplateFields )
)

Change the initial "SELECT * FROM" to "DELETE" after vetting the results of the select.
All after backing up, of course

[nickc]

Code: Select all

SELECT * FROM ac_ProductTemplateFields WHERE InputFieldId NOT IN
( SELECT DISTINCT InputFieldId FROM ac_InputFields )

Same technique to remove orphans in ac_ProductTemplateFields.