Question 272750 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – In SQL, any way to create SELECT for arbitrary numbers?

I would like to have a query with a result like below:

1000
2000
3000

or

1
3
5

so on..I want to control results from lists for instance.

SELECT 1 — I know that I can use SELECT like this for one result line but..

and want to use the result for other queries like below:

INSERT user (number) SELECT …

I want to have users with number of 1000, 2000, 3000 …

Tags:

2

Answers


  1. 0

    Not sure if I understood your question properly.

    But if you are looking for a select statement that generates a sequence without initializing variables;
    the below code might help.

    with recursive rnums as (
  select 1 as n
      union all
  select n+1 as n from rnums
      where n < 10
  )

select concat(n,'000') as n_ from rnums;

    Let us know if you are looking for something else.

    Login or Signup to reply.
  2. 0 
    Hope below query idea can help 

consider you have student table with below inserted row 

create table student
(
    id         bigint auto_increment
        primary key,
    email      varchar(255) null,
    department varchar(255) null,
    name       varchar(255) null
);

INSERT INTO student ( email, department, name) VALUES ( '[email protected]', 'asd', 'John');
INSERT INTO student ( email, department, name) VALUES ( '[email protected]', 'this is test message', 'Rocky');
INSERT INTO student ( email, department, name) VALUES ( '[email protected]', 'THis is test message1', 'Forhad');
INSERT INTO student ( email, department, name) VALUES ( '[email protected]', 'THis is test message1', 'Forhad');
INSERT INTO student ( email, department, name) VALUES ( '[email protected]', 'asd', 'John');
INSERT INTO student ( email, department, name) VALUES ( '[email protected]', 'this is test message', 'Rocky');



now you have another table student_number there you can copy data form student with a number start form 1000

create table student_number
(
    id         bigint auto_increment
        primary key,
    email      varchar(255) null,
    name       varchar(255) null,
    number   int null
);


INSERT INTO student_number (email, name, number)
SELECT email, name, (@row := @row + 1) + 1000 AS number
FROM student
JOIN
(SELECT @row := 0) r
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search