Adding values in a column based on a query result-CodePudding

I have a table:---

id	name	dept
1	Alice	CS
2	Bob	Elect
3	David	Mech.

and a query result:-

id	count
1	100
2	22
3	50

Then I want to add the count column from the query to my original table, something like:-

id	name	dept	count
1	Alice	CSE	100
2	Bob	Elect	22
3	David	Mech.	50

The only I figured out to do, is by storing the query result into a new table and then using UPDATE...SET...WHERE. Is there any way to do it without creating a new table?

CodePudding user response：

First you need to create the count column in tablename using

ALTER TABLE `tablename` ADD COLUMN `nr_count` INT;

Then use:

update tablename t
inner join ( SELECT id, 
                    count(*)  as nr_count 
             FROM tablename 
             GROUP BY id
           ) as t1 
set t.nr_count=t1.nr_count ;