pirusti pirusti - 3 months ago 12
MySQL Question

Select the first rows of a table (that has a column with int values which sum of values is less than the input

My input is

5
in this case, and I want to select the first two rows, to delete the first row and to update the second one, putting the value
7
instead of
10
.

enter image description here

I tried to do this query but it's not enough:

SELECT SUM(`Qty in acquisto`) AS total,`Prezzo in acquisto`
FROM `book`
GROUP BY `Qty in acquisto`
HAVING COUNT(*) >5

Answer

You could use variables to get the rows of interest, together with the information you need to update the records:

SELECT *
FROM   (
    SELECT   `Qty in acquisto`,
             `Prezzo in acquisto`,
             @take := least(`Qty in acquisto`, @needed) as taken,
             `Qty in acquisto` - @take as adjusted_acquisto, 
             @needed := @needed - @take as still_needed
    FROM     book,
             (select @needed := 5) as init
    ORDER BY `Prezzo in acquisto` DESC) base
WHERE  taken + still_needed > 0

The output for the sample data is:

| Qty in acquisto | Prezzo in acquisto | taken | adjusted_acquisto | still_needed |
|-----------------|--------------------|-------|-------------------|--------------|
|               2 |               1000 |     2 |                 0 |            3 |
|              10 |                960 |     3 |                 7 |            0 |

See SQL fiddle

In the innermost query, with alias init, you pass the number of books you need (5 in the example).

So in column adjusted_acquisto you find the value you need to perform the deletes and update:

If that value is 0, delete the corresponding record.

It that value is not 0, update the Qty with that value.

Comments