SQL Error: Can not reopen table

Question

SQL Error: Can not reopen table

Navigation

#1 by (-1 votes)

1

I have a problem when performing an SQL using a temporary table . I need to bring the data da ultima alteração into which the opportunity has 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 the tables are as follows:

opportunity
opportunity_action
opportunity_proposal
[...]

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

Example 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 you can verify that the last change of all opportunities is in the opportunity_account table.

Now transferring this 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 not reopen table: 'a'

I've noticed the MySQL documentation that you can not use any more of 1x a temporary table in a SQL. Otherwise the above SQL 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 me to incorrect information, it 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 me to get back to the biggest change date and on what screen was the change . So 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 accomplish SQL using the temporary table?

mysql

asked by anonymous 14.05.2018 / 23:09

1 answer

Access-Control-Allow-Origin access control on server side Apache PHP How to get a div from another html page with pure js?

score -1 · Answer 1

I do not know if I could handle other situations you have (I would have to know the other tables), but in 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 just ordered the table so that the LARGEST date would occupy the first row and later, I limited the result to displaying a single row (the first)

SQL