SQL update each column values based on query result with join and group by-CodePudding

I have an app table with ID and app name, I want to add a column which contains the number of

table app

|ID app | app name |
|A001   | app01    |
|A002   | app02    |
|A003   | app03    |
|A004   | app04    |
|A005   | app05    |

and a chart table

|ID chart|ID APP|
|C01     |A001  |
|C01     |A002  |
|C02     |A001  |
|C02     |A003  |
|C03     |A004  |
...

and i want to add new column to the app table which containts the total number of chart which an app included in. here's the example :

|ID app | app name |number of chart they're in|
|A001   | app01    |2                         |
|A002   | app02    |1                         |
|A003   | app03    |2                         |
|A004   | app04    |3                         |
|A005   | app05    |1                         |

because app01 is in two chart (C01 and C02), and so on.

i added a new column "number_chart" (number of chart they're in), and i tried this:

update app
set number_chart = (select count(app.id_app)
                    from app
                          inner join Chart on App.ID_App = Chart.ID_App
                    group by app.id_app);

but here's the error that i got :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

how do i solve this? is there any other way? thanks!

CodePudding user response：

According to which database do you use, this query may be different.

But you can use the update statement with select from query

UPDATE [ ONLY ] table [ * ] [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]

Now, for your question, you can use below query:

update app
set number_chart = tmp.count
from (
  select p.id_app, count(p.id_app)
  from 
    app p inner join chart c on p.id_app = c.id_app
  group by p.id_app
) tmp
where app.id_app = tmp.id_app

CodePudding user response：

Try:

update App a 
inner join 
          (  select ID_App,
                    count(id_chart) as 'Nr_of_chart_in'
             from  Chart  
             group by ID_App
          ) as c1 on a.ID_App = c1.ID_App
set a.number_chart = c1.c1.Nr_of_chart_in;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/165

Results for datas in the demo:

ID_App    app_name    number_chart
A001      app01          2
A002      app02          1
A003      app03          1
A004      app04          1
A005      app05          0