How to delete rows in three tables with specific condition (SQL)

Question

Hello. I need to delete the contents of a select that collects rows of three tables, which has columns with common values but which are based on parameters of a single table to exclude.

select *
from tab1,tab2,tab3
where tab1_col=tab2_col and tab1_col=tab3_col and tab1_status=0 and tab1_numero<2020

I can do this select. The problem is that I am not able to delete the selected content.

Thank you

Answer 1

The SELECT can have as many tables as you want, but the command DELETE should be done one for each table.

That should invalidate making one JOIN with that SELECT, or even a subquery, because after the first DELETE, when making the second the result of that SELECT will change, since the data will no longer exist in the table that was made the DELETE.

I suggest entering this data into a temporary table. In your question you do not have the database you are using, so you can not for an example, because the temporary tables are different for databases, but it would be something like this:

-- MySQL: 
CREATE TEMPORARY TABLE IF NOT EXISTS dados_para_excluir AS (aqui dentro o seu select)

-- SQL Server:
SELECT * INTO #dados_para_excluir FROM (aqui dentro o seu select)

Then do the DELETE with each of the tables, something like this:

delete 
  from tab1
 where id in (select id from dados_para_excluir)