0

I have a simple question about the SQL of an UPDATE query. I found something very close to what I want to know here: MySQL: Count occurrences of distinct values

But.. it's not an update query. Here's the example of what I want to do:

In one table (let's call the table "data"), I want to make an UPDATE query. Here’s what the table looks like:

Id color  count
1  blue   0
2  blue   0
3  red    0
4  red    0
5  blue   0
6 white   0

Now, [id] is the auto-incremental key for this example, [color] is a TEXT field. [count] is a number

What I want, is for the count to be UPDATED so that it tells how many instances a [color] occurs. After the UPDATE query runs, the table would look like this:

Id  color count
1   blue  3
2   blue  3
3   red   2
4   red   2
5   blue  3
6   white 1

Looks pretty simple, but I've messed with the DCOUNT and COUNT commands, and I'm probably missing something very easy, but still... no joy. All the help I've seen only only deals with SELECT queries, but I will definitely need this query to update the [count] field.

Thanks in advance!

Community
  • 1
  • 1
  • I think your sall not store this in your table. – Julien Palard Feb 01 '13 at 20:25
  • I'm not understanding what you're trying to say here.. –  Feb 01 '13 at 20:41
  • I don't think that the count of occurencies of something in a table should be stored in a table, you should compute it at runtime, not try to store it (Also it will be very costy to update at each update / insert / delete). – Julien Palard Feb 01 '13 at 20:43
  • Ahh, I understand completely. Normall I would agree with you, and I see your point on why this is reduntant - which, in fact, it is. However, the reason I need a count done is long and convoluded, but it is a required field for the data in this table. This UPDATE would only be done once, so if I HAD to do it, how would it be done? It's basically a COUNTIF statement in EXCEL, and I understand why they do not have it in Access, but I still need to address this problem for this particular database, unfourtunately. heh –  Feb 01 '13 at 20:55

1 Answers1

1

You should try :

UPDATE data JOIN
( 
  SELECT color, count(1) AS color_cnt
  FROM data
  GROUP BY color
) AS sub ON data.color = sub.color
SET cnt = sub.color_cnt
Julien Palard
  • 8,736
  • 2
  • 37
  • 44