I have two related tables, Ad_TypeConsulta and Ad_consulta, a type of query has several queries, I look for the sql script to eliminate both the type and the queries that belong to that type.
DELETE Ad_TipoConsulta, Ad_Consultas FROM Ad_TipoConsulta
JOIN Ad_Consultas ON Ad_Consultas.Sec_TipoConsulta = Ad_TipoConsulta.Sec_TipoConsulta
WHERE Ad_Consultas.Sec_TipoConsulta = 121;
This query generates error in the (,) that separates Ad_TipoConsulta, Ad_Consultas.
To avoid this type of queries, you should set the relationship with the cascading elimination rule, so when you delete a query type, the database engine will only eliminate associated queries,
In the image if and I put the relationship in Cascade, when I delete a workshop, all the orders that belong to that workshop will be deleted.
I know it's not the answer you want since you're explicitly asking for the query, but it's a valid path to take.
I know it's a bit late and you've probably already solved, anyway I answer in case someone else is useful:)
In SQL SERVER it is not possible to delete two tables at the same time using the statement DELETE (I understand that in MySQL if you can), at least until SQL SERVER 2016: according to the documentation ( link ) can only specify a table of target for the elimination.
If you can not configure a cascading elimination rule in the Foreign Key as indicated by Bloodday (I suppose you have one), the only way I see would be to first eliminate the queries that have that type and then the type itself, this in two Separate operations:
DELETE Ad_Consultas WHERE Sec_TipoConsulta= 121; DELETE Ad_TipoConsulta WHERE Sec_TipoConsulta= 121;
And if there is a possibility that one of the deletions will fail, both must be executed in the same transaction (with due error control) to prevent it from being carried out halfway (eg to eliminate the queries, but not the type)
If you want to eliminate from the two tables in the same query you must use a INNER JOIN , for example:
DELETE Ad_TipoConsulta, Ad_Consultas FROM Ad_TipoConsulta INNER JOIN Ad_Consultas