Error when executing a large query

[Ignatius88]

UPDATE movie_temp
SET movie_release_year = (
    substring_index(movie_title,')',1)
)
WHERE movie_title IN (SELECT * FROM (SELECT movie_title FROM movie_temp) AS TEMP);

When executing the above query (movie_temp table has more than 660,000 rows),

after 600 seconds workbench throws me errors

Error Code: 2013Lost connection to MySQL server during query followed by
Error Code: 2006MySQL server has gone away.

I changed max_allowed_packet to 512MB, wait_timeout to 30 but no use.

Can someone help me with this?

[drjohn]

this bit is weird

WHERE movie_title IN (SELECT * FROM (SELECT movie_title FROM movie_temp) AS TEMP);

You are using a sub-sub-query to select movie_title, then using a sub-query to select * from the sub-sub-query, which will give you the exact same result as the sub-sub query - every single movie_title in the entire database
Why????

WHERE movie_title IN (SELECT movie_title FROM movie_temp);
will give you the exact same result.

you've also used an alias for the result of the sub-sub-query, then made no use of it what so ever.

and there is one final itty bitty thing - the entire line five
WHERE movie_title IN (SELECT * FROM (SELECT movie_title FROM movie_temp) AS TEMP);
is not needed anyway....

you've already told it to use movie_temp on the opening line on the first line

UPDATE movie_temp

So then all you have to do is tell it the field to be updated and how to work out the new value,
SET movie_release_year = (
SUBSTRING_INDEX(movie_title,')',1)
)

which reduces the overall query to just

UPDATE movie_temp
SET movie_release_year = (
SUBSTRING_INDEX(movie_title,')',1)
);

you would only use a WHERE condition when only some of the records were to be updated, but you are updating them all. So no need for the WHERE.

[drjohn]

It would be interesting to see some typical values for movie_title, to see how that gives you movie_release_year

[Ignatius88]

Copying Beethoven (2006)
"24" (2001) {Season 4 Prequel (#4.0)}

What I am trying to get is the year in ().

[drjohn]

that makes more sense now