[SOLVED] Count the rows

[santrowithu]

Hello friends,

 

I have a table named myTable with the column names NAME and Degree.

 

The table looks similar to this:

 

NAME | Degree

------------------

AQW | (null)

AQW | (null)

AQW | (null)

PPR | (null)

PPR | (null)

RES | (null)

MEN | (null)

MEN | (null)

MEN | (null)

MEN | (null)

 

I want to get the count of name and update the degree. The table then should look like..

 

NAME | Degree

------------------

AQW | 3

AQW | 3

AQW | 3

PPR | 2

PPR | 2

RES | 1

MEN | 4

MEN | 4

MEN | 4

MEN | 4

 

Can somebody help me please?

 

Santro!

 

I tried the following query

UPDATE mytable AS a SET degree = (SELECT COUNT(NAME) FROM mytable b GROUP BY NAME) WHERE a.name <> NULL;

 

the error is: Error Code : 1093

You can't specify target table 'a' for update in FROM clause

 

[Ken2k7]

I don't know if you can do that in one UPDATE SQL. I think you'll have to run a SELECT and then an UPDATE.

[Philip]

Yeah, you can't update the same table you're selecting from in one query.

[santrowithu]

Okay. Thanks for the help. I will figure out some other option.

[fenway]

Okay. Thanks for the help. I will figure out some other option.

Like a temporary table?

[santrowithu]

Okay. Thanks for the help. I will figure out some other option.

Like a temporary table?

 

Ya, I tried it and it worked.

 

For others with the similar issue, I tried this here..

 

CREATE VIEW bufferView(name,degree)

AS SELECT name,COUNT(*)

FROM myTable

GROUP BY name;

 

UPDATE myTable t

SET degree = (

SELECT degree

FROM bufferView

WHERE name = t.name);

 

DROP VIEW bufferView;

 

Once again, thanks everyone for your help