SQL Error: Can’t reopen table

Question

I have a problem when performing an SQL using a temporary table. I need to bring the data da ultima alteração where the opportunity has been changed.

However, this date comes from several tables for the same opportunity. An opportunity is the header of a request, below it there are other tables that are actions that the opportunity suffered, so that’s the tables:

• opportunity
• expediency
• opportunity_proposal
• [...]

I searched the data of all tables and brought the date of the last change (alteracao_timestamp) of the opportunities.

Example of the records:

oportunidade_id | alteracao_timestamp | tela
3024            | 2015-07-03 00:00:00 | oportunidade
3024            | 2018-02-15 16:18:02 | oportunidade_acao
10930           | 2017-01-05 00:00:00 | oportunidade
10930           | 2018-02-15 16:25:08 | oportunidade_acao
16104           | 2017-05-10 00:00:00 | oportunidade
16104           | 2017-10-03 11:06:00 | oportunidade_acao
16104           | 2017-05-10 00:00:00 | oportunidade_proposta
16104           | 2017-05-26 11:51:00 | oportunidade_compromisso

In this example it is possible to verify that the last change of all opportunities is from the table expediency.

Now transferring it to SQL:

select 
 a.*
from alteracoes_oportunidades a
inner join ( 
   select oportunidade_id, max(alteracao_timestamp) ultima_alteracao
     from alteracoes_oportunidades
    group by oportunidade_id
) b on b.ultima_alteracao = a.alteracao_timestamp 
   and a.oportunidade_id = b.oportunidade_id

Being alteracoes_oportunidades a temporary table, is generating the error:

SQL Error (1137): Can’t reopen table: 'a'

I found in the mysql documentation that it is not possible to use more than 1x a temporary table in an SQL. Otherwise SQL above would solve my problem.

So I had to rewrite SQL to:

select a.oportunidade_id, a.tela, max(a.alteracao_timestamp) as alteracao_timestamp
from alteracoes_oportunidades a
group by a.oportunidade_id

But this returns the incorrect information, is returning me:

oportunidade_id | tela         | alteracao_timestamp
3024            | oportunidade | 2018-02-15 16:18:02
10930           | oportunidade | 2018-02-15 16:25:08
16104           | oportunidade | 2017-10-03 11:06:00

What I need is for you to return me to date of change and on which screen was the change. Then the result should be:

oportunidade_id | tela              | alteracao_timestamp
3024            | oportunidade_acao | 2018-02-15 16:18:02
10930           | oportunidade_acao | 2018-02-15 16:25:08
16104           | oportunidade_acao | 2017-10-03 11:06:00

What would be the correct way to perform SQL using the temporary table?

Answer 1

From the MySQL 8.0 you can use the clause WITH instead of the temporary table:

WITH alteracoes_oportunidades AS (
  -- SELECT que reunirá o conteúdo da, até então, tabela temporária
)
SELECT a.*
  FROM alteracoes_oportunidades a
 INNER JOIN (
   SELECT a1.oportunidade_id,
          MAX(a1.alteracao_timestamp) AS ultima_alteracao
     FROM alteracoes_oportunidades a1
    GROUP BY a1.oportunidade_id
) b ON b.ultima_alteracao = a.alteracao_timestamp
   AND a.oportunidade_id = b.oportunidade_id;

See working on DB Fiddle.

---

WITH Syntax (Common Table Expressions)

A common table Expression (CTE) is a named Temporary result set that exists Within the Scope of a single statement and that can be referred to later Within that statement, possibly Multiple times.

In free translation:

A common table expression (CTE) is a set of named temporary results that exists within the scope of a single instruction and that can be mentioned later in that instruction, possibly several times.

Answer 2

I do not know if you could meet other situations you have (you would have to know the other tables), but for this case in a very simple way you could use:

Select a.oportunidade_id, a.tela, a.alteracao_timestamp
from alteracoes_oportunidades a
ORDER by alteracao_timestamp desc LIMIT 1

I only ordered the table so that the HIGHEST date occupied the first row and later, I limited the result to display a single row ( the first )

SQL