Loop update statement in mysql

Question

I currently trying to update rows using loop in a table.

I have fields named 'id' and 'visitor_id'. 'id' column is auto increment but 'visitor_id' is not. There are already existing data inside the table and both columns look like this.

'id'     'visitor_id'
 1            0
 2            0
 3            0
 4            0
 5            0

So what I want is I want these both columns to have the same value.

'id'     'visitor_id'
 1            1
 2            2
 3            3
 4            4
 5            5

I try and search on how to loop and update the value but still no luck.This is the code that i try.

DELIMITER $$

DROP PROCEDURE IF EXISTS insert_loop $$

CREATE PROCEDURE insert_loop ()
BEGIN
    DECLARE i int DEFAULT 1;
    WHILE i <=30 DO
        UPDATE test_db.visitor_visit SET visitor_id=i WHERE id=i;
        SET i = i + 1;
    END WHILE;
END $$

CALL insert_loop();

I will accept any other method that might help my situation. Thanks! :)

Ghaith Al-Tameemi · Accepted Answer · 2016-11-23 07:57:49Z

3

you have to use the trigger(after insert) in order to solve this problem, try this query:

CREATE TRIGGER aa BEFORE INSERT ON ab FOR EACH ROW SET NEW.visitor_id = new.id

just on your first insert, set the 'visitor_id = null' and then set it in any value you wish because it will directly take the 'id' value in the same row. hope is that useful.

answered Nov 23, 2016 at 7:57

Ghaith Al-Tameemi

3711 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

e4c5 · Accepted Answer · 2016-11-23 03:20:25Z

2

A simple update query can do the same thing.

UPDATE test_db.visitor_visit SET visitor_id=id 
WHERE id BETWEEN 1 and 30

answered Nov 23, 2016 at 3:20

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

1 Comment

etzzz Over a year ago

Omg i can't believe I didnt think about this. I really need to have enough sleep from now on. Thanks!

Collectives™ on Stack Overflow

Loop update statement in mysql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related