CODEWITHSUNDEEP
mysql
Jean-philippe Emond
Jean-philippe Emond

Reputation: 1614

mysql update issues where id in

a have a new issues today..

Don't know why it doesn't works but I'm trying to update table column using a where id in(). I got this error:

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

this is the function:

UPDATE membre_non_filtrer 
SET chef=1 where id IN(
        SELECT tt.id FROM membre_non_filtrer tt INNER JOIN 
        (SELECT famid, MAX(age) AS maxAge 
        FROM membre_non_filtrer GROUP BY famid)
        groupedtt ON tt.famid = groupedtt.famid 
        AND tt.age = groupedtt.maxAge ORDER BY `tt`.`famid` ASC)

Just to tell you that the SELECT works and return a good value.

Any idea?

Upvotes: 1

Views: 56

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

A quick trick would be alias your subquery like IN(SELECT t.id FROM (subquery) t )

UPDATE membre_non_filtrer 
SET chef=1 where id IN(
SELECT t.id FROM       
 SELECT tt.id FROM membre_non_filtrer tt INNER JOIN 
        (SELECT famid, MAX(age) AS maxAge 
        FROM membre_non_filtrer GROUP BY famid)
        groupedtt ON tt.famid = groupedtt.famid 
        AND tt.age = groupedtt.maxAge ORDER BY `tt`.`famid` ASC
) t
)

Or you can use join 

UPDATE 
  membre_non_filtrer tt 
  INNER JOIN 
    (SELECT 
      famid,
      MAX(age) AS maxAge 
    FROM
      membre_non_filtrer 
    GROUP BY famid) groupedtt 
    ON tt.famid = groupedtt.famid 
    AND tt.age = groupedtt.maxAge
 SET tt.chef = 1

Upvotes: 1

Related Questions