Mam tabelę następującej struktury i treści:
Kod
+-------+---------+------------+-------------+
| id | owner | name | value |
|int(15)| int(7) | char(40) | char(40) |
+-------+---------+------------+-------------+
| 1 | 1 | points | 75 |
| 2 | 1 | points_add | 150 |
| 3 | 1 | points_max | 5000 |
| 4 | 1 | costam | jakas_dana |
| 5 | 2 | points | 250 |
| 6 | 2 | points_add | 150 |
| 7 | 2 | points_max | 5000 |
| 8 | 3 | points | 4900 |
| 9 | 3 | points_add | 250 |
| 10 | 3 | points_max | 5000 |
| 11 | 4 | points | 5500 |
| 12 | 4 | points_add | 250 |
| 13 | 4 | points_max | 5000 |
| 14 | 4 | costam | jakas_dana |
+-------+---------+------------+-------------+
| id | owner | name | value |
|int(15)| int(7) | char(40) | char(40) |
+-------+---------+------------+-------------+
| 1 | 1 | points | 75 |
| 2 | 1 | points_add | 150 |
| 3 | 1 | points_max | 5000 |
| 4 | 1 | costam | jakas_dana |
| 5 | 2 | points | 250 |
| 6 | 2 | points_add | 150 |
| 7 | 2 | points_max | 5000 |
| 8 | 3 | points | 4900 |
| 9 | 3 | points_add | 250 |
| 10 | 3 | points_max | 5000 |
| 11 | 4 | points | 5500 |
| 12 | 4 | points_add | 250 |
| 13 | 4 | points_max | 5000 |
| 14 | 4 | costam | jakas_dana |
+-------+---------+------------+-------------+
Wywołuję na niej zapytanie
UPDATE TABLE AS t1, TABLE AS t2, TABLE AS t3 SET t1.value = IF(t1.value > t2.value, t1.value, IF(t1.value+t3.value > t2.value, t2.value, t1.value+t3.value) ) WHERE t2.owner = t1.owner AND t3.owner = t1.owner AND t1.name='points' AND t2.name='points_max' AND t3.name='points_add';
Spodziewany rezultat:
Kod
+-------+---------+------------+-------------+
| id | owner | name | value |
|int(15)| int(7) | char(40) | char(40) |
+-------+---------+------------+-------------+
| 1 | 1 | points | 225 |
| 2 | 1 | points_add | 150 |
| 3 | 1 | points_max | 5000 |
| 4 | 1 | costam | jakas_dana |
| 5 | 2 | points | 400 |
| 6 | 2 | points_add | 150 |
| 7 | 2 | points_max | 5000 |
| 8 | 3 | points | 5000 |
| 9 | 3 | points_add | 250 |
| 10 | 3 | points_max | 5000 |
| 11 | 4 | points | 5500 |
| 12 | 4 | points_add | 250 |
| 13 | 4 | points_max | 5000 |
| 14 | 4 | costam | jakas_dana |
+-------+---------+------------+-------------+
| id | owner | name | value |
|int(15)| int(7) | char(40) | char(40) |
+-------+---------+------------+-------------+
| 1 | 1 | points | 225 |
| 2 | 1 | points_add | 150 |
| 3 | 1 | points_max | 5000 |
| 4 | 1 | costam | jakas_dana |
| 5 | 2 | points | 400 |
| 6 | 2 | points_add | 150 |
| 7 | 2 | points_max | 5000 |
| 8 | 3 | points | 5000 |
| 9 | 3 | points_add | 250 |
| 10 | 3 | points_max | 5000 |
| 11 | 4 | points | 5500 |
| 12 | 4 | points_add | 250 |
| 13 | 4 | points_max | 5000 |
| 14 | 4 | costam | jakas_dana |
+-------+---------+------------+-------------+
Otrzymany rezultat:
Kod
+-------+---------+------------+-------------+
| id | owner | name | value |
|int(15)| int(7) | char(40) | char(40) |
+-------+---------+------------+-------------+
| 1 | 1 | points | 75 | // Tutaj nie podniosło
| 2 | 1 | points_add | 150 |
| 3 | 1 | points_max | 5000 |
| 4 | 1 | costam | jakas_dana |
| 5 | 2 | points | 400 |
| 6 | 2 | points_add | 150 |
| 7 | 2 | points_max | 5000 |
| 8 | 3 | points | 5000 |
| 9 | 3 | points_add | 250 |
| 10 | 3 | points_max | 5000 |
| 11 | 4 | points | 5500 |
| 12 | 4 | points_add | 250 |
| 13 | 4 | points_max | 5000 |
| 14 | 4 | costam | jakas_dana |
+-------+---------+------------+-------------+
| id | owner | name | value |
|int(15)| int(7) | char(40) | char(40) |
+-------+---------+------------+-------------+
| 1 | 1 | points | 75 | // Tutaj nie podniosło
| 2 | 1 | points_add | 150 |
| 3 | 1 | points_max | 5000 |
| 4 | 1 | costam | jakas_dana |
| 5 | 2 | points | 400 |
| 6 | 2 | points_add | 150 |
| 7 | 2 | points_max | 5000 |
| 8 | 3 | points | 5000 |
| 9 | 3 | points_add | 250 |
| 10 | 3 | points_max | 5000 |
| 11 | 4 | points | 5500 |
| 12 | 4 | points_add | 250 |
| 13 | 4 | points_max | 5000 |
| 14 | 4 | costam | jakas_dana |
+-------+---------+------------+-------------+
Ktoś wie, skąd się takie coś bierze?