On Wed, Aug 10, 2011 at 06:23:56PM -0500, Dan McGee wrote:
On Wed, Aug 10, 2011 at 6:08 PM, Dan McGee <dpmcgee@gmail.com> wrote:
On Wed, Aug 10, 2011 at 5:48 PM, Lukas Fleischer <archlinux@cryptocrack.de> wrote:
On Wed, Aug 10, 2011 at 05:35:46PM -0500, Dan McGee wrote:
-- only update votes that wouldn't exist after modification UPDATE PackageVotes SET PackageID = ?merge WHERE PackageID IN (?others) AND UsersID NOT IN ( -- this silly double SELECT works around shitty MySQL not letting you reference the updated table in the update, as it forces materialization of the subquery SELECT * FROM ( SELECT UsersID FROM PackageVotes WHERE PackageID = ?merge ) temp );
That's what I had when I wrote the query for the very first time but that won't work if we merge more than one package at once. Read my next-to-last email in this thread for some more details.
Oh damn it, I thought I set up my test data right- I'll see what I can tweak.
Since deletion/merging isn't exactly something that is done every second, I'm less worried about perfection here. 90% of merges are going to involve one package anyway, so when we have more than one ID, why don't we just run the query once per to-be-deleted package ID? Doing it in sequence will mean each sees the result of the previous and thus dupes won't be created on the 2nd and following merges.
for each ID: UPDATE PackageVotes SET PackageID = ?merge WHERE PackageID = ?todelete AND UsersID NOT IN ( SELECT * FROM ( SELECT UsersID FROM PackageVotes WHERE PackageID = ?merge ) temp );
This will be cross-database, not do anything too funky, and should still be plenty fast.
Well, yeah. I guess this is the best thing we can do here. Either this or using a temporary table. I already spent ~1h trying to construct a proper query that is simple, fast and actually works. Probably, it isn't worth that much work. I can live with a solution that is somewhat less than perfect as well.