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?](https://stackoverflow.com/a/42022625/2548147) ... Otherwise it's not clear to me why you want to do this.. — Raymond Nijland, Sep 07 '19 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, Sep 07 '19 at 15:25
yes you indeed oversimplified as your [example](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=cccd6769aea80e8369933db3de2d2eff) which you comment under Gordan's answer at first seight seams to be simulating `ROW_NUMBER()/RANK() OVER(PARTITION BY ... ORDER BY ...)` — Raymond Nijland, Sep 07 '19 at 15:28

score 2 · Answer 1 · answered Sep 07 '19 at 14:52

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 07 '19 at 14:52

Gordon Linoff

I missed alias only in example. I am getting Error Code: 1264. Out of range value for column '(null)' at row 1 – Kulis Sep 07 '19 at 14:54
1

@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.. – Raymond Nijland Sep 07 '19 at 15:11
1

I probably too much simplified it. My original code has 4 variables and also nested SELECT statements. Here is more realistic version https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=cccd6769aea80e8369933db3de2d2eff – Kulis Sep 07 '19 at 15:19
@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. – Gordon Linoff Sep 08 '19 at 17:22

1 Answers1