There is a table with fields: id | stid | name | price id | stid | name | price id | stid | name | price . What is the best way to combine posts with the same stid in the request, write down the sum of them in the price, and leave only one of these entries?

  • Just in order (in any case there will be 3 requests, you can make 2, but one will still be with one more nested): 1. Say it all with one stid 2. Delete all with one stid 3. Insert a new line - DemoS
  • If just a sample, then select id, stid, name, sum (price) from t group by stid; If you need to update the original page, then a little more difficult. There are different options, but it is desirable to get extra. information about the table. For example id - unique or not? - alexlz
  • @alexlz source page needs to be updated, id is unique. At the moment I am thinking of making several requests (in one update the data, in the other delete). - IVsevolod
  • If I were you, I would make an extra box of bulin box: packaged / unpacked. regularly do the following: 1) select all aydishniki records whose flag = not packed. 2) for them we do the aggregation and insert a new line with the type packed. 3) then by aydishnikam delete records - jmu

1 answer 1

The easiest and safest - will do in two stages.

one.

Create exactly the same (in structure) table with the MEMORY engine (if you are worried about a lot of anxiety, do it on a standard engine so that it is saved on the disk).

Do something like

 INSERT INTO MEMORY_TABLE(stid, name, price) select stid, name, max(price) from oldTabel group by stid, name

If for one stid, the name can be several, then the name is selected by the subquery as you need (group by will be only by stid).

2

At this stage, you have the opportunity to completely verify the correctness of the data.

Delete all records from the old table.

In a similar query, overtake from memory into the old table.

Drop a memory table.

PS Of course, this is not very suitable for regular automated operations, but has proven itself very well for one-time manual rework. Especially when the tables are more complicated - than you have now.

PPS To do better, when users do not rummage in a DB. And do not forget to make bakap, before you do))

  • the problem is that this automated action is performed regularly. - IVsevolod
  • one
    If it is, you need to do it regularly, that is, there are errors in the database architecture. Perhaps you need another, final table, where you will store data in the context of stid, price. Update with triggers for example. And the question is, why can't sum (price) + group by stid be used in select queries? - SilverIce
  • If there is a window in user service, then there should be no problems. By cron'u run the script. - alexlz
  • Your table, is it generally what are the positions of orders or shipment? Bo something is badly thought out, where else would it be necessary to summarize the prices. - SilverIce
  • @SilverIce And the question is, why can't sum (price) + group by stid be used in select queries? How can not? (And in mysql, you can even take fields that are not participating in a group without group functions: max, sum, etc. Although this, of course, is perverted) - alexlz