Update with inner query selecting the same table

Question

Update with inner query selecting the same table

Navigation

#1 by (1 votes)

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 when executing:

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

What could be wrong?

mysql query

asked by anonymous 30.07.2014 / 01:32

1 answer

How to catch parameters by XSL? Swipe to the right after left gives error ANDROID

score 1 · Answer 1

Your SQL is a bit disorganized.

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

So I understand you want to update the records of the cp_feedback_trabalho table that dhEnvio is non-null and where the id_pessoa_que_enviou of the b table is equal to the id_pessoa_que_recebeu of the a table and id_pessoa_que_recebeu of table b is equal to id_pessoa_que_enviou of table a and id_projeto of table b is equal id_projeto of table a and dhEnvio of table b is different of null.

My God, I roll.

In SQL it's 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.