Advanced Mysql Query Error

[seventheyejosh]

I am getting this error:

 

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

 

From this query:

 

UPDATE
`users`
SET
`users`.`distinctions`=CONCAT(`users`.`distinctions`,'12,')
WHERE
`users`.`id`
IN(
    SELECT
    DISTINCT `abstract_authors`.`userid`
    FROM
    `abstract_authors`
    INNER JOIN
    `users`
    ON
    `users`.`id`=`abstract_authors`.`userid`
    WHERE
    `abstract_authors`.`abstractid`
    IN(
        SELECT
        `abstracts`.`id`
        FROM
        `abstracts`
    )
)

 

Can anyone enlighten me as to what is going on?

 

Basically I need to update the users table. I'm doing the first IN() to get all of the author id's, and the second IN() (top down) to get only the author entries that have an active abstract. Some of the abstracts when deleted weren't removing their author ties, initially.

 

Thanks a lot!

 

- Josh

[seventheyejosh]

Well i googled a bit, and figured it out. Since I was already backing up the big users table, I just referenced it instead in the inner IN();

 

UPDATE
`users`
SET
`users`.`distinctions`=CONCAT(`users`.`distinctions`,'12,')
WHERE
`users`.`id`
IN(
    SELECT
    DISTINCT `abstract_authors`.`userid`
    FROM
    `abstract_authors`
    INNER JOIN
    `users_backup_jo_0421` AS `users_2`
    ON
    `users_2`.`id`=`abstract_authors`.`userid`
    WHERE
    `abstract_authors`.`abstractid`
    IN(
        SELECT
        `abstracts`.`id`
        FROM
        `abstracts`
    )
)