Mippy - 8 months ago 27

SQL Question

I've asked this question in the morning, but did not get any answer for this. So I delete previous one, and ask this again cause I am getting stuck for a long while. Hope you guys can help me.

I have a table named

`overtime`

`| total | remain | t_date |`

|-------|--------|---------------------|

| 3 | 0 | 2016-01-01 12:20:00 |

| 4 | 0 | 2016-02-01 13:10:00 |

| 2 | 0 | 2016-03-01 14:40:00 |

| 3 | 0 | 2016-04-01 10:20:00 |

| 5 | 2 | 2016-05-01 17:20:00 |

I want to update column remain

`order by t_date desc`

`| total | remain | t_date |`

|-------|--------|---------------------|

| 5 | 5 | 2016-05-01 17:20:00 | -- remain will be updated to 5 cause total = 5, then $h(6) = $h(9) - (total(5) - remain(2))

| 3 | 3 | 2016-04-01 10:20:00 | -- remain will be updated to 3 cause total = 3, then $h(3) = $h(6) - (total(3) - remain(0))

| 2 | 2 | 2016-03-01 14:40:00 | -- remain will be updated to 2 cause total = 2, then $h(1) = $h(3) - (total(2) - remain(0))

| 4 | 1 | 2016-02-01 13:10:00 | -- remain will be updated to 1 cause $h only has 1, then $h will be 0

| 3 | 0 | 2016-01-01 12:20:00 | -- cause $h = 0, this row has no need to be updated

The sample data is like above, what I want to do is update column

`remain`

`remain`

- update order is . For example, I must update
`order by t_date desc`

row first, then`2016-05-01 17:20:00`

, then`2016-04-01 10:20:00`

and so on...`2016-03-01 14:40:00`

- Parameter is 9, it will be allocated to every row, and should be updated to
`remain`

's value. For example, the first row`total`

,`2016-05-01 17:20:00`

= 5 and`total`

= 2, so`remain`

will be updated to 5, and parameter will minus`remain`

-`total`

, it will be 6 and do the next row's allocation, till row`remain`

, the paramter is 1, so this row's`2016-02-01 13:10:00`

only needs to updated to 1. And another row will have no need to updated.`remain`

If there is any unclear point for question, please leave a comment, I can explain that.

Any help is appreciated. Thanks in advance.

Answer

**SELECT QUERY:**

```
SELECT
*,
IF((IF(@h <= 0,0,IF(@h >= total,total, @h)) + remain) <= total, (IF(@h <= 0,0,IF(@h >= total,total, @h)) + remain), (IF(@h <= 0,0,IF(@h >= total,total, @h)))) allocated,
@h := @h - (total - remain)
FROM overtime
CROSS JOIN (SELECT @h := 9) var
ORDER BY t_date DESC
```

**UPDATE QUERY:**

```
UPDATE
overtime OT
INNER JOIN
(
SELECT
*,
IF((IF(@h <= 0,0,IF(@h >= total,total, @h)) + remain) <= total, (IF(@h <= 0,0,IF(@h >= total,total, @h)) + remain), (IF(@h <= 0,0,IF(@h >= total,total, @h)))) allocated,
@h := @h - (total - remain)
FROM overtime
CROSS JOIN (SELECT @h := 9) var
ORDER BY t_date DESC
) AS t
ON OT.t_date = t.t_date
SET OT.remain = t.allocated;
```

*Demo shows the table data sorted by descending order of date after being updated by the above update query.*

**More:**