Create table using SELECT statement with variable in MySQL

Question

How can I create a Table based on SELECT statement with variable? I mean something like (simplified example):

    SET @tmp = 0; 

    CREATE TABLE test AS

    SELECT
        (@tmp:=@tmp + 1) test,
        ...
    FROM x

I use MySQL 5.7.

i have a feeling this is a workaround as it is not possible to generate row_number/sequence numbers in a MySQL view with user variables? ... Otherwise it's not clear to me why you want to do this.. — Raymond Nijland
– Raymond Nijland, Commented Sep 7, 2019 at 15:06
In general yes, but my code has also to handle some exceptions, so I couldn't just use this example. SQL code is working but I would like to create a table for simplification. — Kulis
– Kulis, Commented Sep 7, 2019 at 15:25
yes you indeed oversimplified as your example which you comment under Gordan's answer at first seight seams to be simulating ROW_NUMBER()/RANK() OVER(PARTITION BY ... ORDER BY ...) — Raymond Nijland
– Raymond Nijland, Commented Sep 7, 2019 at 15:28

Gordon Linoff · Accepted Answer · 2019-09-07 14:52:39Z

2

Your code should work. You just need to give the column a name:

SET @tmp = 0; 

CREATE TABLE test AS
    SELECT (@tmp := @tmp + 1) as test_id
        ...
    FROM x;

You can combine this into a single statement:

CREATE TABLE test AS
    SELECT (@tmp := @tmp + 1) as test_id
        ...
    FROM x CROSS JOIN
         (SELECT @tmp := 0) params;

Here is a db<>fiddle

answered Sep 7, 2019 at 14:52

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kulis Over a year ago

I missed alias only in example. I am getting Error Code: 1264. Out of range value for column '(null)' at row 1

Raymond Nijland Over a year ago

@Kulis if you want to workaround the problem/error you can't use MySQL user variables in a view like i have explained in the comment under the question which feels like you are trying to do here.. Then using CREATE TEMPORARY TABLE test AS(..) instead might make more sense..

Kulis Over a year ago

I probably too much simplified it. My original code has 4 variables and also nested SELECT statements. Here is more realistic version dbfiddle.uk/…

Gordon Linoff Over a year ago

@Kulis . . . I would suggest that you ask a new question, with appropriate sample data, desired results, and the SQL Fiddle. This question has been answered.

Collectives™ on Stack Overflow

Create table using SELECT statement with variable in MySQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related