Query UPDATE entire table from COUNT (MYSQL)

Asked

Viewed 67 times

0

Hello,

I’m new to programming, and I have a slight background in databases. I’d like some help, since I haven’t found a case that looks like mine.

Scenario: I’m setting up a workshop environment with students, where: WORKSHOP (which contain customized tables) ---> (generates)--->>> Students (associated by WORKSHOP ID in custom tables). A FATHER record generates CHILDREN Records, only that I need in the database, through a PROCEDURE, the same carry out the counting of these children records and update a field with the counting in all PARENTS records according to the query. I’m using the following query (MYSQL):

update calls_cstm cc, calls c, insof_lista_presenca lp,
 (select count(distinct lp.name) count from insof_lista_presenca lp, calls c
    where lp.data_inicio = DATE(lp.data_hora_inicio)
    and lp.reference_id = c.id
    and lp.deleted = 0
    and c.deleted =0
     group by lp.evento_id)
     as count 
    set cc.total_inscritos_c = count
    where cc.id_c = c.id
    and c.deleted = 0
    and c.status = 'Planned'
    and c.direction = 'Inbound';

It turns out that this query is recording in the field that I want the first record, not recording in the remaining PARENTS records. In other words it is only repeating the first record. Result of query by select:

# count, evento_id, data_inicio
2, 20-0013, 2020-01-04
2, 20-0014, 2020-01-04
2, 20-0015, 2020-01-05
2, 20-0017, 2020-01-04
4, 20-0020, 2020-01-04
2, 20-0021, 2020-01-06
2, 20-0021, 2020-01-07

The count itself is certain, however, only happens what I mentioned above. I believe my UPDATE is wrong...

Can someone help me?

mysql

  • Try to pass all the subquey to the set: ... set cc.total_inscritos_c = (select ...

  • If it is a field calculated from other database data it should not exist as a physical field of your database. If you think it necessary to make life easier for users you can create a view containing this calculated field. For more details see the data normalization process.

  • @Costamilam the result is the same because the filter happens in Where, if you notice I have a filter filter... and as the system I am editing is open, this does not have views to use... the access is direct in the bank.

1 answer

1


Solved! I found the inserting solution JOIN ... ON, follows the solution below:

UPDATE calls_cstm cc
  JOIN ( SELECT lp.reference_id
              , count(lp.name) AS sum_attr
           FROM insof_lista_presenca lp , calls c
           where  lp.deleted = 0
           and lp.reference_id = c.id
           and c.deleted = 0
           and c.status = 'Planned'
           and c.direction = 'Inbound'
          GROUP BY lp.reference_id
       ) r
    ON r.reference_id = cc.id_c
   SET cc.total_inscritos_c = r.sum_attr
   WHERE cc.id_c in (select c.id from calls c);

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.