CODEWITHSUNDEEP
mysqldatabase
etzzz
etzzz

Reputation: 165

Loop update statement in mysql

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! :)

Upvotes: 3

Views: 6804

Answers (2)

Ghaith Al-Tameemi
Ghaith Al-Tameemi

Reputation: 370

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.

Upvotes: 3

e4c5
e4c5

Reputation: 53734

A simple update query can do the same thing.

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

Upvotes: 2

Related Questions