CODEWITHSUNDEEP
mysql
Krish
Krish

Reputation: 39

count one column and update another in same table in mysql

I want to update one column based on count of another column in same table. I tried below query but no luck.

update
 table
set conntype='Multiple'
where (select COUNT(cpemac) as nos from table group by cpemac) > 1

I get following error:

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

Upvotes: 0

Views: 209

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

In MySQL, you need to use a JOIN:

update t join
       (select cpemac, count(cpemac) as nos
        from t
        group by cpemac
       ) tt
       on t.cpemac = tt.cpemac
    set t.conntype = 'Multiple'
    where cnt > 1;

This is a specific limitation of MySQL.

I should note, however, that your version would not work in any database. It would either update all rows or no rows, depending on the result of the subquery. There is no connection between the subquery and the outer query.

Upvotes: 1

Related Questions