Bugzilla – Attachment 47810 Details for
Bug 15675
Add issue_id column to accountlines and use it for updating fines
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 15675 [QA Followup] - Switch from NOT IN to LEFT JOIN
Bug-15675-QA-Followup---Switch-from-NOT-IN-to-LEFT.patch (text/plain), 1.77 KB, created by
Kyle M Hall (khall)
on 2016-02-09 14:39:32 UTC
(
hide
)
Description:
Bug 15675 [QA Followup] - Switch from NOT IN to LEFT JOIN
Filename:
MIME Type:
Creator:
Kyle M Hall (khall)
Created:
2016-02-09 14:39:32 UTC
Size:
1.77 KB
patch
obsolete
>From 44f3d8c4ea12be9111dc84ad6e8cda45a69fc975 Mon Sep 17 00:00:00 2001 >From: Kyle M Hall <kyle@bywatersolutions.com> >Date: Tue, 9 Feb 2016 14:38:57 +0000 >Subject: [PATCH] Bug 15675 [QA Followup] - Switch from NOT IN to LEFT JOIN > >--- > 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 f133705..89aead4 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.1.4
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 15675
:
47313
|
47314
|
47315
|
47316
|
47373
|
47374
|
47375
|
47376
|
47378
|
47379
|
47380
|
47600
|
47601
|
47810
|
48468
|
48469
|
48470
|
48471
|
48472
|
48473