Update with Inner query by selecting the same table

Asked

Viewed 2,650 times

1

I have the following query:

UPDATE cp_feedback_trabalho as a SET
    a.visivel = 1
WHERE a.dhEnvio IS NOT NULL AND EXISTS (
    SELECT 
        b.id 
    FROM cp_feedback_trabalho as b 
        WHERE 
            b.id_pessoa_que_enviou = a.id_pessoa_que_recebeu AND 
            b.id_pessoa_que_recebeu = a.id_pessoa_que_enviou AND 
            b.id_projeto = a.id_projeto AND
            b.dhEnvio IS NOT NULL
)

I get the following error while running:

#1093 - You can’t specify target table 'a' for update in FROM clause

What could be wrong?

mysql query

  • What do you want to do? You can also write SQL (in Portuguese and not in SQL, just to understand), like what you want to do, because I don’t understand which relations.

  • Hello Fernando, I just want to update a field (visible) based on another record that happens to be in the same table that will be updated (cp_feedback_work). The problem is in the variables (a and b)

  • To my knowledge, in Mysql, it is not possible to update a table and use it as a criterion in the clause WHERE. A suggestion would be to use a temporary table with the result of a SELECT of the same table with the clause WHERE update (if possible of course).

1 answer

1

Your SQL is a little disorganized.

And the error there is already giving a hint, "you are not specifying the target table, where you will update".

From what I understand you want to update the table records cp_feedback_trabalho that dhEnvio is different from null and where the id_pessoa_que_enviou table b is the same as id_pessoa_que_recebeu table a and the id_pessoa_que_recebeu table b is the same as id_pessoa_que_enviou table a and the id_projeto table b be equal id_projeto table a and the dhEnvio table b is different from null.

My God that roll.

In SQL is simpler (Hehe):

UPDATE a
SET a.visivel = 1
FROM cp_feedback_trabalho AS a
INNER JOIN cp_feedback_trabalho AS b ON a.id = b.id
WHERE a.dhEnvio IS NOT NULL
  AND b.id_pessoa_que_enviou = a.id_pessoa_que_recebeu
  AND b.id_pessoa_que_recebeu = a.id_pessoa_que_enviou
  AND b.id_projeto = a.id_projeto
  AND b.dhEnvio IS NOT NULL

Look there and see if it works.

Browser other questions tagged mysql query

You are not signed in. Login or sign up in order to post.