How to convert a table?

Question

MySQL

There is a table:

How to convert it using SQL, so that:
-After the conversion, the id2 line will be deleted, in the id1 line, the value will become -8 (ie the sum of id1.value and id2.value).
Just indicate with what help to do this or in which direction to "dig", I want to figure out how this is done.
UPD1. Suppose there are many such strings, i.e. you need to go through all the records in a loop and do
idN.value = idN.value + idN+1.value;
delete idN+1
UPD2. Only lines where flag = in
UPD3 are removed. Lines out, in alternate, there are many of them.

Answer 1

well, let's assume that you have only one record = 'out'
whereas in the variant we divide the task into 3
1. calculate the sum of value for in select sum(value) from .... where flag = 'in'
2. subtract this sum from the record = 'out'
3. deleting records where flag = 'in'
pseudo query like this
begin transaction
update tableName
set value = value + (select sum(value) from tableName where flag = 'in')
where flag = 'out'
delete tableName where flag = 'in'
commit transaction

Answer 2

Using the SQL language, the statements:
select ...
update ...
delete ..