mysql Creating a temporary table not working...undeclared variable error...whats wrong with my code?

Question

When I run the code below inside phpmyadmin I get an error "Undefined Variable tmpportfolio"

what is the problem?

SQL:

CREATE TEMPORARY TABLE tmpportfolio (p_name VARCHAR(255),portfolio_id INT UNSIGNED NOT NULL);

SELECT p_name, portfolio_id INTO tmpportfolio
    FROM portfolio
    WHERE parent_portfolio_id IS NULL
    AND portfolio_id NOT IN
        (
           SELECT x.parent_portfolio_id
           FROM portfolio x
           WHERE x.parent_portfolio_id IS NOT NULL
        )
    AND account_id = 1

SELECT * FROM tmpportfolio;

Victor Nicollet · Accepted Answer · 2010-10-14 17:11:06Z

2

SELECT field INTO variable loads the value of the field into a variable. This is why it's complaining about the variable not being defined.

What you need to do is use an INSERT INTO table (colummn) SELECT field statement instead. For instance:

INSERT INTO tmpportfolio (p_name, portfolio_id) 
SELECT p_name, portfolio_id 
FROM portfolio
WHERE parent_portfolio_id IS NULL
AND portfolio_id NOT IN
    (
       SELECT x.parent_portfolio_id
       FROM portfolio x
       WHERE x.parent_portfolio_id IS NOT NULL
    )
AND account_id = 1

answered Oct 14, 2010 at 17:11

Victor Nicollet

24.7k4 gold badges62 silver badges90 bronze badges

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

Comments

Levon Mirzoyan · Accepted Answer · 2010-10-14 17:15:35Z

1

Just join first two SQL statement in one CREATE TABLE ... SELECT statment:

CREATE TABLE tmpportfolio SELECT p_name, portfolio_id FROM portfolio
    WHERE parent_portfolio_id IS NULL
    AND portfolio_id NOT IN
        (
           SELECT x.parent_portfolio_id
           FROM portfolio x
           WHERE x.parent_portfolio_id IS NOT NULL
        )
    AND account_id = 1

SELECT * FROM tmpportfolio;

answered Oct 14, 2010 at 17:15

Levon Mirzoyan

4611 gold badge2 silver badges7 bronze badges

Comments

Zabba · Accepted Answer · 2010-10-14 17:09:58Z

1

You cannot use INTO to insert records into a table in MySql (in SQL Server, you do use INTO that way). In MySql, INTO is used to put a value into a variable.

By doing the SELECT fields INTO tmpportfolio statement, MySql thinks that tmpportfolio is now a variable and it cannot find the variable in the SELECT * ... The fix is to change the insert-records sql to something like:

INSERT INTO tmpportfolio(p_name,portfolio_id) SELECT ....;

answered Oct 14, 2010 at 17:09

Zabba

65.6k48 gold badges184 silver badges214 bronze badges

Comments

Darko Maksimovic · Accepted Answer · 2019-09-16 10:52:06Z

1

The confusion seems to arise from W3Schools explaining how to do "select into table" without mentioning it's specific to some (non-Mysql) implementations. A page on dev.mysql specifically denies this possibility on Mysql, by saying:

MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension. Instead, MySQL Server supports the INSERT INTO ... SELECT standard SQL syntax, which is basically the same thing.

answered Sep 16, 2019 at 10:52

Darko Maksimovic

1,29313 silver badges16 bronze badges

Comments

HLGEM · Accepted Answer · 2010-10-14 17:05:43Z

0

I can't speak for mysql sepcifically but usually once you have created a table you insert into it it not use selct into which creates the table (and which can't since the table already exists.)

answered Oct 14, 2010 at 17:05

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Collectives™ on Stack Overflow

mysql Creating a temporary table not working...undeclared variable error...whats wrong with my code?

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related