List:General Discussion« Previous MessageNext Message »
From:Rob Pollock Date:August 20 2012 12:10am
Subject:Re: How to loop a query that is only able to handle 1 ROW at a time?
View as plain text  
Hi Incarus,

You don't need a loop just a correlated subquery on the update:

UPDATE TB1 SET Ivet=PREG_REPLACE('/TeXT/', '', Ivet);


Example:

mysql> CREATE TABLE blah(value INTEGER, square INTEGER);
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO blah(value) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM blah;
+-------+--------+
| value | square |
+-------+--------+
|     1 |   NULL |
|     2 |   NULL |
|     3 |   NULL |
|     4 |   NULL |
|     5 |   NULL |
+-------+--------+
5 rows in set (0.00 sec)

mysql> UPDATE blah SET square=POW(value,2);
Query OK, 5 rows affected (0.07 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM blah;
+-------+--------+
| value | square |
+-------+--------+
|     1 |      1 |
|     2 |      4 |
|     3 |      9 |
|     4 |     16 |
|     5 |     25 |
+-------+--------+
5 rows in set (0.00 sec)

On Mon, Aug 20, 2012 at 10:23 AM, Incarus Derp <icarusderp@stripped> wrote:

> I have a two line query that is only able to handle 1 row per
> execution. Could any of you give me some insight on how I could loop this
> per every single row in the table? The query in question is:
>
> SELECT PREG_REPLACE('/TeXT/', '' , Ivet) FROM `DB1`.`TB1` INTO @VAR12;
> UPDATE `TB1` SET Ivet = @VAR12;
>

Thread
How to loop a query that is only able to handle 1 ROW at a time?Incarus Derp19 Aug
  • Re: How to loop a query that is only able to handle 1 ROW at a time?Rob Pollock20 Aug