CODEWITHSUNDEEP
mysqldatabasestored-proceduresauto-increment
J. Dev
J. Dev

Reputation: 95

How to update not-null column value to auto-increment column value (mysql)?

I have one table orders with two columns:

  1. id(auto_increment key field)
  2. order_id (not null field).

I want to update order_id with the value of id field means if id = 1 then order_id = 1, if id = 2 then order_id = 2 so on.....

How can I do that with MySQL stored procedure?

Thanks for the help.

Upvotes: 1

Views: 230

Answers (1)

wchiquito
wchiquito

Reputation: 16551

One option is:

mysql> DROP TABLE IF EXISTS `orders`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `orders` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `order_id` INT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER //

mysql> DROP PROCEDURE IF EXISTS `new_procedure`//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `new_procedure`()
    -> BEGIN
    ->   DECLARE `last_insert_id` INT UNSIGNED;
    ->   INSERT INTO `orders` (`id`, `order_id`)
    ->   VALUES (NULL, 0);
    ->   SET `last_insert_id` := LAST_INSERT_ID();
    ->   UPDATE `orders`
    ->   SET `order_id` = `last_insert_id`
    ->   WHERE `id` = `last_insert_id`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `new_procedure`();
Query OK, 1 row affected (0.00 sec)

mysql> CALL `new_procedure`();
Query OK, 1 row affected (0.00 sec)

mysql> CALL `new_procedure`();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
    ->   `id`,
    ->   `order_id`
    -> FROM
    ->   `orders`;
+----+----------+
| id | order_id |
+----+----------+
|  1 |        1 |
|  2 |        2 |
|  3 |        3 |
+----+----------+
3 rows in set (0.00 sec)

Upvotes: 1

Related Questions