On Wed, Aug 10, 2011 at 06:23:56PM -0500, Dan McGee wrote: > On Wed, Aug 10, 2011 at 6:08 PM, Dan McGee <[email protected]> wrote: > > On Wed, Aug 10, 2011 at 5:48 PM, Lukas Fleischer > > <[email protected]> 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.
