Problem with Table UPDATE based on summation of another table

Question

Greetings to all of the forum!

Like everyone who starts learning Mysql, we come across relatively simple problems but we can’t solve.

I created the INFO_PACOTES table that stores information of packages that traffic in my network. The files are processed via shell and stored only the source MAC and the package size.

mysql> desc INFO_PACOTES;

| Field       | Type       |Null | Key |Default | Extra |

|mac_origem   |char(17)    |YES  |     | NULL   |       |

|tamanho_frame|decimal(5,0)|YES  |     | NULL   |       |

As a normal network, several devices are connected and monitored. Thus, there will be several devices with different MAC’s.

I created the KNOWN table that is described below. The only thing I need is to add the values of the column size_frame of the INFO_PACOTES table grouped by different "mac_origin" and the result of the sums update the total trafego_field of the KNOWN table.

|Field       |Type          |Null|Key|Default|Extra|


|mac         |char(17)      |NO  |PRI|NULL   |     |



|trafego_total|decimal(20,0)|YES |   |NULL   |     |

I created the following sentence below:

UPDATE CONHECIDOS, INFO_PACOTES
    SET trafego_total =  (SELECT sum(tamanho_frame) FROM INFO_PACOTES 
    GROUP BY INFO_PACOTES.mac_origem) 
    WHERE CONHECIDOS.mac=INFO_PACOTES.mac_origem;

At the end of the sentence, select results in more than one line, generating the error:

ERROR 1242 (21000): Subquery Returns more than 1 Row

Can anyone tell me what I’m missing?

Thank you!

Answer 1

I don’t know why you put

Update Conhecidos, InfoPacotes

since only the Known table will be updated,

and Where, has to stay inside the sub-select. See:

UPDATE CONHECIDOS SET trafego_total =  (SELECT sum(tamanho_frame) FROM INFO_PACOTES GROUP BY INFO_PACOTES.mac_origem where INFO_PACOTES.mac_origem =  CONHECIDOS.mac);

this way all the records in the Known table will be updated at once.

Answer 2

His logic was almost right. Two details were missing.

You don’t want to do an update based on a merge. You just want to do it based on a grouping. You want the total traffic of a given MAC address to be updated according to the sum of all the packages that went through that MAC.

In Mysql 8.0, there is CTE support, which would look more or less like the code:

WITH soma_trafego AS (
    SELECT
       mac_origem AS mac,
       SUM(tamanho_frame) AS total
    FROM
       info_pacotes
    GROUP BY
        mac_origem
 )
 UPDATE
     conhecidos
 SET
      trafego_total = (SELECT total FROM soma_trafego WHERE soma_trafego.mac = conhecidos.mac)

Another form with CTE that I think is valid (I haven’t been able to test yet):

WITH soma_trafego AS (
    SELECT
       mac_origem AS mac,
       SUM(tamanho_frame) AS total
    FROM
       info_pacotes
    GROUP BY
        mac_origem
 )
 UPDATE
     conhecidos, soma_total
 SET
      conhecidos.trafego_total = soma_total.total
WHERE
     soma_trafego.mac = conhecidos.mac

If you are using a version of Mysql that does not yet support CTE, I recommend leaving only subquey and removing the second table from update. Basically, it’s the same as the first CTE, but, let’s say, CTE is expanded in the subquery:

UPDATE
     conhecidos
 SET
      trafego_total = (SELECT SUM(info_pacotes.tamanho_frame)  FROM  info_pacotes WHERE info_pacotes.mac_origem = conhecidos.mac GROUP BY info_pacotes.mac_origem)

About your query

Note that you reference the use of the packet information table twice. The first time, it is in the context of the table conhecidos, and ensures that it will only update if there is someone whose MAC matches with the MAC of some package.

The second time you use the table, it is in the column context of a row. You group by source MAC, but in no time filter this MAC. By not filtering the MAC, you return multiple lines for this update. If you only do the subquery, you will see this happening. As there are multiple rows returned in the context of a column of a row, the DBMS gets lost and throws this warning to you.