Problem with Table UPDATE based on the sum of another table

Question

Problem with Table UPDATE based on the sum of another table

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

Greetings to all of the forum!

As everyone who starts learning MySQL, we come across relatively simple problems that we can not solve.

I created the INFO_PACOTES table that stores packet information that travels on my network. The files are processed via the shell and stored only the source MAC and 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 MACs.

I created the KNOWN table that is described below. The only thing I need is to add the values of the column_frame column of the INFO_PACOTES table grouped by different "mac_origem" and the result of the sums update the field total_traffic of the table NAME.

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


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



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

I have 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!

mysql sql-update

asked by anonymous 06.05.2017 / 22:39

2 answers

Python with django is giving problem when migrating a template Help with script to draw routes

score 0 · Answer 1

I do not know why you put it

Update Conhecidos, InfoPacotes

Since only the Known table will suffer the update,

and the where, must be within 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);

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

score 0 · Answer 2

His logic was almost right. Missing two details.

You do not want to do an update based on a join. You want to do only based on a grouping. You want the total traffic of a given MAC address to be updated according to the sum of all the packets that passed through that MAC.

In MySQL 8.0, there is CTE support, which would look something like this:

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 way with CTE that I think is valid (I could not test it 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 the subquey and removing the second table from update . Basically, it's the same as the first CTE, but, let's say, the 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 conhecidos table, and guarantees that it will only update if there is someone whose MAC matches the MAC of a package.

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