CODEWITHSUNDEEP
mysqlsql
Cheetah Felidae
Cheetah Felidae

Reputation: 999

How to update the value of the least value row with the value of the maximum value row using MySQL?

I have a table which has a combination of item_id and payment_id columns as a key.

Every two rows have the same item_id value.

This is how the table looks.

item_id  payment_id  amount
      1     140       1000
      1     141       3000

      2     141        500
      2     145        600

      3       4       4000
      3     735       9000

How to subtract the amount value of the least payment_id row from the amount value of the maximum payment_id row (of the two rows with the same item_id) using MySQL?

To clarify, this is how the table I want.

item_id  payment_id  amount
      1     140       1000
      1     141       2000 : 3000 - 1000

      2     141        500
      2     145        100 :  600 -  500

      3       4       4000 
      3     735       5000 : 9000 - 4000

Cheer!

Upvotes: 0

Views: 47

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31812

You can get the new amount with this query:

select p1.item_id, p1.payment_id, p1.amount - (
    select p0.amount
    from payments p0
    where p0.item_id    = p1.item_id
      and p0.payment_id < p1.payment_id
    order by p0.payment_id
    limit 1
) as new_amount
from payments p1
having new_amount is not null;

It will subtract the amount of the "last" row with the same item_id (if present).

You can then use that query in the UPDATE statement as derived table joined to your original table:

update payments p
join (
    select p1.item_id, p1.payment_id, p1.amount - (
        select p0.amount
        from payments p0
        where p0.item_id    = p1.item_id
          and p0.payment_id < p1.payment_id
        order by p0.payment_id
        limit 1
    ) as new_amount
    from payments p1
    having new_amount is not null    
) p1 using (item_id, payment_id)
set p.amount = p1.new_amount;

Demo: http://rextester.com/DJD86481

Upvotes: 1

Mihai
Mihai

Reputation: 26784

UPDATE tt JOIN (SELECT item_id, MAX(payment_id) mp , (SUBSTRING_INDEX(GROUP_CONCAT(amount ORDER BY payment_id DESC),',',1) - SUBSTRING_INDEX(GROUP_CONCAT(amount ORDER BY payment_id ),',',1))  maxdif FROM tt GROUP BY item_id) s  
ON tt.item_id=s.item_id
SET tt.amount =s.maxdif
WHERE tt.payment_id =s.mp AND  tt.item_id=s.item_id;



SELECT * FROM tt;

See it working

Upvotes: 1

Related Questions