CODEWITHSUNDEEP
mysqlsqlstored-procedures
xdiver
xdiver

Reputation: 111

How to loop the mysql procedures?

I have table_a which have these following records:

+----+---+---+
| a  | b | c |
+----+---+---+
|  1 | 1 | 2 |
|  2 | 1 | 2 |
|  3 | 1 | 2 |
|  4 | 1 | 2 |
|  5 | 1 | 2 |
|  6 | 1 | 2 |
|  7 | 1 | 2 |
|  8 | 1 | 2 |
|  9 | 1 | 2 |
| 10 | 1 | 2 |
+----+---+---+

How can I add another column(d) which the number of records are only 4 wherein the value are the product of loop statement? I'm using mysql stored procedure for this kind of sql.

These are the output that I wanted to make

+----+---+---+---+
| a  | b | c | d |
+----+---+---+---+
|  1 | 1 | 2 | 1 |
|  2 | 1 | 2 | 2 |
|  3 | 1 | 2 | 3 |
|  4 | 1 | 2 | 4 |
|  5 | 1 | 2 |   |
|  6 | 1 | 2 |   |
|  7 | 1 | 2 |   |
|  8 | 1 | 2 |   |
|  9 | 1 | 2 |   |
| 10 | 1 | 2 |   |
+----+---+---+---+

Upvotes: 0

Views: 42

Answers (2)

John Ruddell
John Ruddell

Reputation: 25872

on a side note if the values inside of b are something other than 1 and this is just for an example then you can do multiplication like so

SELECT a, b, c, IF(@a <= 4, a * b, '') as d, @a := @a + 1
FROM table_a
CROSS JOIN (SELECT @a := 1) t

or to make it easier if column a is an incrementing id then just use a like so

SELECT a, b, c, IF(a <= 4, a * b, '') as d
FROM table_a

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can get the results just by doing:

select a, b, c, (case when a <= 4 then a end) as d
from table t;

You can add the column and set the value as:

alter table add column d unsigned;

update table t
    set d = a
    where d <= a;

I see no need to loop in a stored procedure.

Upvotes: 3

Related Questions