From 64e31a5c428b27918d258342e6b9db6119132e54 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 9 Feb 2016 14:38:57 +0000 Subject: [PATCH] Bug 15675 [QA Followup] - Switch from NOT IN to LEFT JOIN Signed-off-by: Brendan Gallagher brendan@bywatersolutions.com --- installer/data/mysql/atomicupdate/bug_15334.sql | 12 +++++++++++- 1 file changed, 11 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/atomicupdate/bug_15334.sql b/installer/data/mysql/atomicupdate/bug_15334.sql index f133705f18..89aead4c3e 100644 --- a/installer/data/mysql/atomicupdate/bug_15334.sql +++ b/installer/data/mysql/atomicupdate/bug_15334.sql @@ -5,7 +5,17 @@ ALTER TABLE accountlines ADD issue_id INT(11) NULL DEFAULT NULL AFTER accountlin UPDATE accountlines LEFT JOIN issues USING ( itemnumber, borrowernumber ) SET accounttype = 'F' WHERE accounttype = 'FU' and issues.issue_id IS NULL; -- Close out any extra not really accruing fines, keep only the latest accring fine -UPDATE accountlines SET accounttype = 'F' WHERE accountlines_id NOT IN ( SELECT accountlines_id FROM ( SELECT * FROM accountlines WHERE accounttype = 'FU' ORDER BY accountlines_id DESC ) a2 GROUP BY borrowernumber, itemnumber ); +UPDATE accountlines a1 + LEFT JOIN (SELECT MAX(accountlines_id) AS keeper, + borrowernumber, + itemnumber + FROM accountlines + WHERE accounttype = 'FU' + GROUP BY borrowernumber, itemnumber + ) a2 USING ( borrowernumber, itemnumber ) +SET a1.accounttype = 'F' +WHERE a1.accounttype = 'FU' + AND a1.accountlines_id != a2.keeper; -- Update the unclosed fines to add the current issue_id to them UPDATE accountlines LEFT JOIN issues USING ( itemnumber ) SET accountlines.issue_id = issues.issue_id WHERE accounttype = 'FU'; -- 2.39.5