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;

Answer 1

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.)

Answer 2

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 ....;

Answer 3

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

Answer 4

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;