CODEWITHSUNDEEP
mysqlsql
gator
gator

Reputation: 3523

Update by row number in MySQL; foreach row?

I want to update every row of a table.

SET DELIMITER ;;
DECLARE i INT DEFAULT 0;
DECLARE n INT DEFAULT 0;
SET i = 0;
SELECT COUNT(*) FROM table1 INTO n;
WHILE i < n DO
    UPDATE table1
        SET col1 = col1 + col2
        WHERE ROW_NUMBER = i;
    SET i = i + 1;
END WHILE;;

ROW_NUMBER above being something I find to find. I'm not sure how to find the row number of a tuple.

How do I update with a WHERE clause using a row number?

SET DELIMITER ;;
CREATE TABLE table1 (
    col1 INT,
    col2 INT
);;

INSERT INTO table1 VALUES
    (5, 2),
    (2, 6),
    (3, 7);;

+----+----+
|col1|col2|
+----+----+
|  5 |  2 |
|  2 |  6 |
|  3 |  7 |
+----+----+

Expected outcome would be:

+----+----+
|col1|col2|
+----+----+
|  7 |  2 |
|  8 |  6 |
| 10 |  7 |
+----+----+

Upvotes: 0

Views: 233

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

If you want to increment all values of col1 by col2, just do:

UPDATE table
    SET col1 = col1 + col2;

That's all. SQL is a set-based language. There is no reason to do an explicit loop for this type of operation.

Upvotes: 2

Related Questions