How to do a multi-table update with a limit in MySQL
According to the MySQL documentation, you can’t do a multi-table UPDATE with a LIMIT.
What’s a multi-table update with a limit? Well, something like this:
UPDATE
foo
, bar
SET
foo.baz=bar.baz
WHERE
foo.baz IS NULL
AND foo.id=bar.id
LIMIT
1000
;
(Which doesn’t work. Of course, you can do single table UPDATEs with a LIMIT just fine)
Why would you even want to do this?
Well, anytime you have monster sized tables, and you don’t want to lock everybody else while you either read (from bar) or write (to foo). If you can put a limit on the update, you can call it repeatedly, in small chunks, and not choke everything for all other users.
For example, if bar happens to have, say, ohhh, 30 million rows in it, foo happens to have ooh, 2 million rows and they’re both used by everything, all the time.
So, here’s a sneaky way to get around this limitation. I did promise one, right there in the title, after all.
UPDATE
foo
, (SELECT
bar.id
, bar.baz
FROM
foo
, bar
WHERE
foo.id=bar.id
AND foo.baz IS NULL
LIMIT
1000
) tmp
SET
foo.baz=tmp.baz
WHERE
foo.id=tmp.id
;
Some important notes:
- The update conditions (foo.baz IS NULL) go inside the subquery, along with the LIMIT.
- We have to match ids twice – once for the subquery, and once against the created temporary table. That’s why we make sure we SELECT both the id and baz from bar in the subquery.
- There’s no conditionals (other than id match) on the outside WHERE condition, since we’ve done them all in the subquery.
- MySQL also has a limitation of not allowing you to UPDATE while SELECTing from the same table in a subquery. Notice that this sneakily avoids it by only SELECTing from the other table.
So, how about that? You can now do limited multi-table updates.
Oh, except for one. Minor. Problem.
This doesn’t work with temp tables (eg if foo was created with a CREATE TEMPORARY TABLE statement).
Bugger.
However, here’s a sneaky way around that limitation too.
First of all, give your temp table another column, in the example below “ref_no INT”.
Make sure you have an index on the id, otherwise it’ll be dog slow.
Then do this:
# do this in chunks of 1k
SET @counter = 1;REPEAT
UPDATE
tmp_foo
SET
ref_no=@counter
WHERE
ref_no=0
AND baz IS NULL
LIMIT
1000
;
COMMIT;UPDATE
tmp_foo
, bar
SET
tmp_foo.baz=bar.baz
WHERE
tmp_foo.ref_no=@counter
AND tmp_foo.id=bar.id
;
COMMIT;SET @counter = @counter + 1;
UNTIL (SELECT COUNT(id) FROM tmp_foo WHERE ref_no=0 AND baz IS NULL) = 0
END REPEAT;
Some important notes:
- We’re basically flagging a thousand rows at a time, then matching only against those rows – pretty simple concept really.
- The commits are in there because MySQL can be a bit weird about not propagating changes to the database if you don’t commit inside your stored proc. This ensures that updates are passed out, which also means I can run multiple copies of this stored proc concurrently with moderate safety (if I replace @counter with a suitably large RAND() value) – well, as much as you can normally expect with MySQL anyway.
- If you want to reuse the temp table (say, to update something else from – a reverse update to that shown above) you’ll need to reset all the ref_no’s to 0.
- Whatever conditions are in the initial WHERE need to be mirrored in the final SELECT COUNT.
- Obviously just drop the table when you’re finished.
As a bonus, I’ve found this is actually quicker than doing one single large scale update. Why? Less memory is used.
So look at that. TWO ways to get multi-table updates with a limit. Nifty.
related
How to automatically position PuTTY terminal
PuTTY is arguably the best Windows terminal app. I’ve used it for years.
One thing that has always felt missing is the ability to automatically position it on the screen on startup. For example, I always have a couple of windows where I watch certain processes. They’re always in the same position on my screen, and always running the same scripts inside them. How do I do that?
So, you can imagine my glee when I found a patch out there that will do exactly that. Props go to Brad Goodman who wrote it. Follow that link to get both an exe (his patch applied to a 2005 build) and the source so you can patch it yourself. I’ve also put a copy of the exe here, in case his site disappears.
How to use it? Very simple.
- Save your session settings from inside PuTTY, as normal
- Start up putty-bkg.exe (instead of the usual putty.exe)
- Now when you load your sessions you’ll also see the new “Position & Icon” options, under “Window”:
Enter the desired top, left (& icon, if you feel inspired)
- Alter them to your heart’s content (don’t forget to also adjust the rows & columns under “Window” to get it the right size too), save your session and voila.
Even more useful is if you combine this with setting a remote command to be run at initial connection.
- Click on “SSH” under “Connection” (you can’t do this if the session is already active)
- Enter the command or script you want run in the “Remote Command” textbox:
Enter the startup command
- Click the “Save” button (under “Session”) as usual.
Now, to run all of the above, just setup a shortcut to:
“C:\Program Files\putty\putty-bkg.exe” -load [session name]
(where [session name] is whatever you saved your session as). Obviously adjust the path to wherever you put putty.bkg.exe.
Next time you click the shortcut, not only will PuTTY be positioned correctly on the screen, but it’ll also automatically be running whatever script you desire. Just like magic! (but with less jiggery pokery)