2

I have a table with pairs of matching records that I query like this:

select id,name,amount,type from accounting_entries 
  where name like "%05" and amount != 0 order by name limit 10;

Results:

+------+----------------------+--------+-------+
| id   | name                 | amount | type  |
+------+----------------------+--------+-------+
|  786 | D-1194-838HELLUJP-05 |  -5800 | DEBIT | 
|  785 | D-1194-838HELLUJP-05 |  -5800 | DEBIT | 
| 5060 | D-1195-UOK4HS5POF-05 |  -5000 | DEBIT | 
| 5059 | D-1195-UOK4HS5POF-05 |  -5000 | DEBIT | 
|  246 | D-1196-0FUCJI66BX-05 |  -7000 | DEBIT | 
|  245 | D-1196-0FUCJI66BX-05 |  -7000 | DEBIT | 
| 9720 | D-1197-W2J0EC1BOB-05 |  -6500 | DEBIT | 
| 9719 | D-1197-W2J0EC1BOB-05 |  -6500 | DEBIT | 
| 2694 | D-1198-MFKIKHGW0S-05 |  -5500 | DEBIT | 
| 2693 | D-1198-MFKIKHGW0S-05 |  -5500 | DEBIT | 
+------+----------------------+--------+-------+
10 rows in set (0.01 sec)

I need to perform an update so that the resulting data will look like this:

+------+----------------------+--------+--------+
| id   | name                 | amount | type   |
+------+----------------------+--------+--------+
|  786 | D-1194-838HELLUJP-05 |  -5800 | DEBIT  | 
|  785 | C-1194-838HELLUJP-05 |   5800 | CREDIT | 
| 5060 | D-1195-UOK4HS5POF-05 |  -5000 | DEBIT  | 
| 5059 | C-1195-UOK4HS5POF-05 |   5000 | CREDIT | 
|  246 | D-1196-0FUCJI66BX-05 |  -7000 | DEBIT  | 
|  245 | C-1196-0FUCJI66BX-05 |   7000 | CREDIT | 
| 9720 | D-1197-W2J0EC1BOB-05 |  -6500 | DEBIT  | 
| 9719 | C-1197-W2J0EC1BOB-05 |   6500 | CREDIT | 
| 2694 | D-1198-MFKIKHGW0S-05 |  -5500 | DEBIT  | 
| 2693 | C-1198-MFKIKHGW0S-05 |   5500 | CREDIT | 
+------+----------------------+--------+--------+
10 rows in set (0.01 sec)

One entry should negate the other entry. It doesn't matter if I update the first or second matching record, what matters is that one has a positive amount and the other has a negative amount. And the type and name need to be updated.

Any clues on how to do this? What would the update command look like? Maybe using a group by clause? I have some ideas on how to do it with a stored procedure, but can I do it with a simple update?

Tauren
  • 26,795
  • 42
  • 131
  • 167

3 Answers3

4

Try this: 

UPDATE accounting_entries as ae
SET    name = 'C' + SubString(name, 1, Length(name) - 1))
       amount = amount * -1
       type = 'Credit'    
WHERE  id = 
         (SELECT MIN(id) FROM 
            (SELECT * FROM accounting_entries) as temp
          GROUP BY name)

The key is the subquery in the WHERE section that limits the updates to the lowest ID of each name value. The assumption is that the lower ID is the one that you will always want to update. If this is not correct, then update the subquery based on whatever rule you would use.

Edit: Update to subquery based on technique found here, due to limitation on mysql defined here.

This query gives a method for updating all records at once (as it seemed like this is what the OP was looking for. However, the most efficient way to do this would be to enumerate through all records in code (php, asp.net, etc), and through code-based methods update the rows that needed to change. This would eliminate the performance issues inherent with running updates off of subqueries in mysql.

Community
  • 1
  • 1
Yaakov Ellis
  • 40,752
  • 27
  • 129
  • 174
  • In theory, this looks like it would work, but I'm getting an error. `ERROR 1093 (HY000): You can't specify target table 'accounting_entries' for update in FROM clause` when I execute this query: `update accounting_entries set name = 'C' + substring(name,2,length(name) - 1), amount = - amount, type = "CREDIT" where id in (select min(ae.id) from accounting_entries as ae where name like "%-05" and amount != 0 group by ae.name);` – Tauren May 17 '11 at 07:30
  • Updated subquery with a different way at getting the same result. Hopefully this will work. – Yaakov Ellis May 17 '11 at 07:44
  • @Yaakov: Yes, that should work. However, it is *SLOW*! It basically creates a temp table with all records for each iteration. My table isn't even that large (35000 rows), but this would take days to complete. I experimented with a query that would only update one record and it it took 37 seconds to run. Here is the query I'm running: `update accounting_entries set name = concat("C",substring(name,2)), amount = - amount, type = "CREDIT" where id in (select min(id) from (select id from accounting_entries) as temp where name like "%-05" and amount != 0 group by name);` – Tauren May 17 '11 at 08:21
  • I found a similar article on using this technique: http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/ – Tauren May 17 '11 at 08:22
  • Here's the query that just updates one record: `update accounting_entries set name = concat("C",substring(name,2)), amount = - amount, type = "CREDIT" where id in (select min(id) from (select id from accounting_entries where name like "%-05" and amount != 0) as temp group by name);` MySQL reports: `Query OK, 1 row affected (37.65 sec) Rows matched: 1 Changed: 1 Warnings: 0` – Tauren May 17 '11 at 08:23
  • Well, it looks like I was mistaken on how long it would take. The following query finished in about 6 minutes. Maybe its because only 869 rows needed updating, not 35000. The data appears to be correct as well! `update accounting_entries set name = concat("C",substring(name,2)), amount = - amount, type = "CREDIT" where id in (select min(id) from (select id,name,amount from accounting_entries) as temp where name like "%-05" and amount != 0 group by name);` Results: `Query OK, 869 rows affected (5 min 56.97 sec) Rows matched: 869 Changed: 869 Warnings: 0` – Tauren May 17 '11 at 08:36
  • Do you have an index on the table by the [name] column? That might help speed things up. Aside from that, you might get better efficiency out of doing all of the actual sorting and update logic through code (asp.net, php, etc) rather than trying to get it all done in one mysql query. – Yaakov Ellis May 17 '11 at 08:37
1

If the ID:s for a pair always match the formula x and x+1, you could say something like 

WHERE MOD(`id`, 2) = 1

EDIT: I haven't tested this code, so I can't guarantee that it's possible to put a column name into a MOD like this, but it might be worth a try, and/or further investigation.

Adrian Schmidt
  • 1,886
  • 22
  • 35
  • That might work, but I can't guarantee they always match x an x+1. This is a multiuser system, so entries could be created concurrently. I wonder how I could check if the ids are consecutive? – Tauren May 17 '11 at 07:12
  • Ah, that's a pity. It would have been a nice solution otherwise I guess... I'll keep thinking about it :) – Adrian Schmidt May 17 '11 at 07:14
  • 1
    Adrian - actually, using `id % 2 = 1` works fine. When I select the count of all records and compare it with the count of `id % 2 = 1` records, I get exactly half the count. So it looks like my ids are x and x+1. Since this is a one-time data fix, this solution might just work. But it would be nice to find a solution that doesn't make me worry that these rules won't work. We won't be applying the fix for another day or two, so new data might change things. – Tauren May 17 '11 at 07:35
  • Adrian, I almost selected your answer because in my case it would have worked, but didn't because there was no guarantee that records wouldn't get missed. It was by far a **much** faster query than the selected answer. – Tauren May 17 '11 at 08:39
  • No problem, happy to be of service :) – Adrian Schmidt May 17 '11 at 09:59
0

Does this constraint hold true all the time (D == -C) ?

If so, you do not need to keep redundant data in your table, store only one "amount" value (for example the debit):

786 | 1194-838HELLUJP-05 | -5800

and then, on the application level, append a D- to the name and get the raw amount or append a C- and get the - amount.

Costi Ciudatu
  • 37,042
  • 7
  • 56
  • 92
  • I realize the amount doesn't need to have negative values if debit/credit is stored also, but that isn't something we can change at the application level right now. Thanks for the suggestion though! – Tauren May 17 '11 at 07:28