Posted by Andy Hassall on 06/22/06 23:20

On Fri, 23 Jun 2006 00:39:03 +0200, Gerwazy <gerwazy@nomail.no> wrote:

>One of field of my database is amount.
>How can I update (set mark="X") as many rows as sum of the amount give
>me <=20? (in Id order DESC)
>
>|id| amount |
>|1 | 8 | update this
>|2 | 7 | this
>|3 | 3 | and this
>|4 | 11 | but not this (>20!)

MySQL won't let you update a table referenced in a subquery, at least not in
the version I'm on (4.1), so have to go via temporary table. Maybe 5.0 allows
this directly.

mysql> create temporary table tx as
-> select t1.id
-> from t t1
-> join t t2 where (t2.id <= t1.id)
-> group by t1.id
-> having sum(t2.amount) <= 20;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> update t
-> set t.mark = 'x'
-> where t.id in (select id from tx);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> drop table tx;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+--------+------+
| id | amount | mark |
+------+--------+------+
| 1 | 8 | x |
| 2 | 7 | x |
| 3 | 3 | x |
| 4 | 11 | NULL |
+------+--------+------+
4 rows in set (0.00 sec)

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Back to original message]