Can't update existing records MySQL

Question

I've created a new column (third one) and all records are null in that column. The table has 100 rows. When I try INSERT INTO, records are added but only from 101 row and previous ones are still null. That's why I decided to use UPDATE, but I have the next error message: You can't specify target table 'actors' for update in FROM clause

Here is my query:

UPDATE
    actors
SET
    starred_count = (
        SELECT COUNT(actors.Actor_id)
        FROM starred
        INNER JOIN actors ON actors.Actor_id = starred.Actor_id
        GROUP BY starred.Actor_id
        ORDER BY starred.Actor_id
    )

How could I do this properly?

You're setting every row's `starred_count` to the same value. You probably mean to do a multi-table update with a join clause. — tadman, Oct 31 '18 at 15:57
I'm not 100% sure about the logic of the UPDATE, but have a look here at Derived Tables https://dev.mysql.com/doc/refman/5.7/en/derived-tables.html. — J2112O, Oct 31 '18 at 16:36
I tried this solutions: [link](https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause/12737534) but result is still the same :/ — Mark, Oct 31 '18 at 16:45

score 0 · Accepted Answer · answered Oct 31 '18 at 17:25

0

Give a try to next query, since on MySQL updating using a query is not valid.

UPDATE
    actors AS a
INNER JOIN
    (SELECT Actor_id, COUNT(*) AS starred_count
     FROM starred
     GROUP BY Actor_id) AS s ON s.Actor_id = a.Actor_id
SET
    a.starred_count = s.starred_count

answered Oct 31 '18 at 17:25

Shidersz

16,846
2
23
48

Ohh it works. Now i have to carefully analyse my mistake. – Mark Oct 31 '18 at 17:46

Can't update existing records MySQL

1 Answers1