CODEWITHSUNDEEP
mysql
Peon
Peon

Reputation: 8020

Mysql update with joined data

I have this simple update query:

UPDATE
    erif_notify e
SET
    e.current_amount = '0.00'
    ,e._date = now()
WHERE
    id IN ( SELECT
                en.id
            FROM
                u_contracts uc
            JOIN
                erif_notify en
            ON
                en.contract_id = uc.id
            WHERE
                uc._status = 7
          )

But it gives me such error:

You can't specify target table 'e' for update in FROM clause

Why does it give me this?

I know I can just do it with two separate queries, but can it be done in one?

Thank you in advance.

Upvotes: 0

Views: 62

Answers (4)

GautamD31
GautamD31

Reputation: 28763

Edit like this ans try

UPDATE
erif_notify as e
SET
e.current_amount = '0.00'
,e._date = now()
WHERE
id IN ( SELECT
            en.id
        FROM
            erif_notify en                
        JOIN
            u_contracts uc
        ON
            en.contract_id = uc.id
        WHERE
            uc._status = 7
      )

Upvotes: 0

Travesty3
Travesty3

Reputation: 14469

Here we go...this answer is tested and works:

SAMPLE

UPDATE
    erif_notify e
    INNER JOIN u_contracts uc
        ON e.contract_id = uc.id
        AND uc._status = 7
SET
    e.current_amount = '0.00',
    e._date = NOW();

Upvotes: 1

Germann Arlington
Germann Arlington

Reputation: 3353

I read your query couple of times and...
is not it identical to 

UPDATE
    erif_notify e
SET
    e.current_amount = '0.00'
    ,e._date = now()
WHERE
    e.contract_id IN ( SELECT
                uc.id
            FROM
                u_contracts uc
            WHERE
                uc._status = 7
          )

Upvotes: 0

John Woo
John Woo

Reputation: 263693

I think you need double subquery on this,

UPDATE
    erif_notify e
SET
    e.current_amount = '0.00' ,
    e._date = now()
WHERE
    id IN ( SELECT x.ID FROM 
              (
                 SELECT  en.id
                 FROM u_contracts uc
                      JOIN  erif_notify en
                         ON en.contract_id = uc.id
                 WHERE uc._status = 7
              ) x
          )

"The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating."

Upvotes: 1

Related Questions