CODEWITHSUNDEEP
mysqlsqlmysql-5.7
Kulis
Kulis

Reputation: 1010

Create table using SELECT statement with variable in MySQL

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.

Upvotes: 0

Views: 334

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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

Upvotes: 2

Related Questions