SQL Update + Inner Join on a range of rows

Question

I have table "Temp" and table "Today", with same column names ("url" and "date").

I want to update "date" column of "Temp" table when url match. But my tables are quite big (30K elements) and phpmyadmin does not want to execute the following - right - query :

update Temp Tp
inner join Today Ty on
    Tp.url = Ty.url
set Tp.date = Ty.date

I get a "Query execution was interrupted, error #1317" Why ? I expect this is because I pay for a mutualized server (OVH) and I am not able to execute queries longer than 2-3 seconds.

Anyway, now I want to execute this query range by range. First 1000 rows, 1000-2000 etc.

I tried the following :

update Temp Tp
    inner join 
    (
        select Tp2.date
        from Temp Tp2
            inner join Today Ty2 
                on Tp2.url = Ty2.url
        limit 1000
    ) Ty on Tp.url = Ty.url                     
set Tp.date = Ty.date

BUT I get the following error : #1054 - Unknown column 'Ty.url' in 'on clause'

I couldn't find out why ?

PS : I used this thread : http://stackoverflow.com/questions/20558649/mysql-update-join-with-limit to write the second query but I reach my limits... — Vincent, Sep 28 '15 at 21:29
Because your subquery only has one column... "Ty.date". You need to add the Ty.url to your select statement. — pmbAustin, Sep 28 '15 at 21:31
Thank you @pmbAustin, I add "Tp2.url" after "Tp2.date" on line 4 near the SELECT. Last question : do you confirm I am doing an inner join between FULL Temp and LIMITED Ty ? — Vincent, Sep 28 '15 at 21:40
@M.Ali, not sure about the answer, I am hosted at OVH and I use phpmyadmin interface, I guess this is MySQL — Vincent, Sep 28 '15 at 21:44

score 1 · Accepted Answer · answered Sep 28 '15 at 22:07

As far as I can see, there are two problems here. First, as already mentioned by @pmbAustin, you're missing a column in your subquery.

Secondly, I think your subquery should be selecting the date from Ty2, rather than Tp2:

update Temp Tp
    inner join 
    (
        select Ty2.date, Tp2.url
        from Temp Tp2
            inner join Today Ty2 
                on Tp2.url = Ty2.url
        limit 1000
    ) Ty on Tp.url = Ty.url                     
set Tp.date = Ty.date

See SQLFiddle (although for practical reasons, this demo is limited to 2).

Although you haven't specifically asked this (and you're probably aware already), for completeness it should be mentioned that for subsequent queries, LIMIT should be used alongside OFFSET (or just use the shortcut LIMIT 1000, 1000, LIMIT 2000,1000, LIMIT <offset>, <limit>, etc.

Thanks ! But I still miss something : with "limit 100" the query takes 1.5 second, but with "limit 20000, 2" I have a query interruption... Why is it so long for 2 rows ?! — Vincent, Sep 28 '15 at 22:17
The higher the offset, the longer it takes unfortunately. See [this answer](http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down) for more details, and tips to improve performance. — Michael McMullin, Sep 28 '15 at 22:22

SQL Update + Inner Join on a range of rows

1 Answers1