CODEWITHSUNDEEP
mysqlsqlphpmyadminsql-insertrecursive-query
Pablo
Pablo

Reputation: 23

SQL how to increment value by 1 in multiple rows

I'm manually adding this request 

INSERT INTO `wp_gdlrpayment` (`id`, `course_id`, `student_id`, `author_id`, `payment_info`, `price`, `coupon_discount`, `payment_status`, `payment_date`, `attachment`, `attendance`, `attendance_section`, `last_section`) VALUES (NULL, '79848', '52417', '1', 'a:11:{s:10:\"first_name\";s:3:\"\";s:9:\"last_name\";s:14:\"\";s:5:\"email\";s:24:\"\";s:5:\"phone\";s:9:\"\";s:7:\"address\";s:7:\"\";s:15:\"additional_note\";s:0:\"\";s:6:\"amount\";s:1:\"1\";s:5:\"price\";s:1:\"0\";s:6:\"coupon\";s:9:\"cursodani\";s:15:\"coupon-discount\";i:159;s:4:\"code\";s:12:\"AP2810313994\";}', '0.0000', '159.0000', 'paid', '2016-03-07 21:59:43', NULL, '2017-01-13 17:35:12', '7', NULL)

I need to update the 3rd value yousee "52417" by one to a definite number, in this case for example until reaching 52778.

Right now I'm doint it manually like this:

INSERT INTO `wp_gdlrpayment` (`id`, `course_id`, `student_id`, `author_id`, `payment_info`, `price`, `coupon_discount`, `payment_status`, `payment_date`, `attachment`, `attendance`, `attendance_section`, `last_section`) VALUES (NULL, '79848', '52417', '1', 'a:11:{s:10:\"first_name\";s:3:\"\";s:9:\"last_name\";s:14:\"\";s:5:\"email\";s:24:\"\";s:5:\"phone\";s:9:\"\";s:7:\"address\";s:7:\"\";s:15:\"additional_note\";s:0:\"\";s:6:\"amount\";s:1:\"1\";s:5:\"price\";s:1:\"0\";s:6:\"coupon\";s:9:\"cursodani\";s:15:\"coupon-discount\";i:159;s:4:\"code\";s:12:\"AP2810313994\";}', '0.0000', '159.0000', 'paid', '2016-03-07 21:59:43', NULL, '2017-01-13 17:35:12', '7', NULL), (NULL, '79848', '52418', '1', 'a:11:{s:10:\"first_name\";s:3:\"\";s:9:\"last_name\";s:14:\"\";s:5:\"email\";s:24:\"\";s:5:\"phone\";s:9:\"\";s:7:\"address\";s:7:\"\";s:15:\"additional_note\";s:0:\"\";s:6:\"amount\";s:1:\"1\";s:5:\"price\";s:1:\"0\";s:6:\"coupon\";s:9:\"cursodani\";s:15:\"coupon-discount\";i:159;s:4:\"code\";s:12:\"AP2810313994\";}', '0.0000', '159.0000', 'paid', '2016-03-07 21:59:43', NULL, '2017-01-13 17:35:12', '7', NULL)

but there is any way to generate the SQL request by incrementing +1 that specific value not by hand?

Upvotes: 0

Views: 577

Answers (1)

GMB
GMB

Reputation: 222722

If you are running MySQL 8.0, you can use a recursive common table expression to generate the numbers, and then do the insert:

INSERT INTO `wp_gdlrpayment` (
    `course_id`, 
    `student_id`, 
    `author_id`, 
    `payment_info`, 
    `price`, 
    `coupon_discount`, 
    `payment_status`, 
    `payment_date`, 
    `attendance`, 
    `attendance_section`
)
WITH t AS (
    SELECT 52417 student_id
    UNION ALL SELECT student_id + 1 WHERE student_id < 52778
)
SELECT
    79848, 
    student_id, 
    1, 
    'a:11:{s:10:\"first_name\";s:3:\"\";s:9:\"last_name\";s:14:\"\";s:5:\"email\";s:24:\"\";s:5:\"phone\";s:9:\"\";s:7:\"address\";s:7:\"\";s:15:\"additional_note\";s:0:\"\";s:6:\"amount\";s:1:\"1\";s:5:\"price\";s:1:\"0\";s:6:\"coupon\";s:9:\"cursodani\";s:15:\"coupon-discount\";i:159;s:4:\"code\";s:12:\"AP2810313994\";}', 
    0.0000, 
    159.0000, 
    'paid', 
    '2016-03-07 21:59:43', 
    '2017-01-13 17:35:12', 
    7
FROM t

Side notes:

  • I removed the columns to which NULL values were assigned from the query - presumably, removing them does not make a difference, and it makes the query shorter

  • I removed the single quotes around the values that look like numbers - if these columns really are strings, then you can add them back, otherwise this avoids the need for implicit conversion

Upvotes: 1

Related Questions