0

I am working on an export of data from MySQL to .csv where I need to include a row count based on a specific column in the table.

I have a table called e with the columns id, e1 and e2

|id|e1|e2|
| 1| A|  |
| 2| A|  |
| 3| A|  |
| 4| B|  |
| 5| C|  |
| 6| B|  |

The result I'm looking for is:

|id|e1|e2|
| 1| A| 3|
| 2| A| 3|
| 3| A| 3|
| 4| B| 2|
| 5| C| 1|
| 6| B| 2|

If I try something like this: 

UPDATE `e` SET `e2` = (SELECT COUNT(`id`) FROM `e` WHERE `e1` = 'A') WHERE `e1` = 'A';

I get: You can't specify target table 'e' for update in FROM clause

How can I go about updating my data so that the value in e2 is the COUNT of the rows with the value from e1? Is this something I can't/shouldn't do in MySQL but should be doing in PHP?

(I feel like I'm missing something obvious....)

Jason
  • 15,017
  • 23
  • 85
  • 116
  • Some ideas: http://stackoverflow.com/q/16909732/395857 Basically you cannot have a read access to `e` while you are updating it (write lock), so you need to circumvent it, e.g. using temporary tables. – Franck Dernoncourt Jul 07 '13 at 16:09

3 Answers3

2
UPDATE e a, (SELECT e1,COUNT(*) cnt 
               FROM e 
           GROUP BY e1) b

   SET a.e2 = b.cnt
 WHERE a.e1 = b.e1;
chetan
  • 2,876
  • 1
  • 14
  • 15
0
with
AAA AS
(
    select e1,count(*) as cnt from e group by e1
)

update e
set e2 = AAA.cnt
from e
inner join AAA ON e.e1 = AAA.e1
asafrob
  • 1,838
  • 13
  • 16
0

Try Following Query : 

update e
set e2 = a.cnt
from e inner join (select e1,count(*) as cnt from e as a group by e1) as a ON e.e1 = A.e1
Hiren Dhaduk
  • 2,760
  • 2
  • 19
  • 21