📋

Description

This error occurs when you attempt to create or alter a MySQL view whose `SELECT` statement refers to a temporary table. MySQL views are persistent database objects and cannot depend on temporary tables, which are session-specific and transient.

💬

Error Message

View's SELECT refers to a temporary table '%s'

🔍

Known Causes

3 known causes

⚠️

Direct Temporary Table Reference

The `CREATE VIEW` statement explicitly includes a `SELECT` query that targets a temporary table.

⚠️

Indirect Reference via Subquery/CTE

A subquery or Common Table Expression (CTE) within the view's definition attempts to select from a temporary table.

⚠️

View Based on Temp Table Logic

Attempting to encapsulate logic that relies on temporary tables directly within a view definition.

🛠️

Solutions

3 solutions available

1. Recreate View Without Temporary Table Reference medium

Modify the view definition to directly query the underlying base tables instead of a temporary table.

1

Identify the temporary table used in the view definition. You can often find this by examining the `CREATE VIEW` statement or by running `SHOW CREATE VIEW view_name;` and looking for references to tables that start with `#sql_` or are explicitly declared as temporary.

SHOW CREATE VIEW your_view_name;

2

Determine the logic used to populate the temporary table. This usually involves a `CREATE TEMPORARY TABLE ... AS SELECT ...` statement.

Example of how the temporary table might be created:
CREATE TEMPORARY TABLE temp_user_data AS
SELECT user_id, COUNT(*) AS login_count FROM user_logins GROUP BY user_id;

3

Rewrite the view's `SELECT` statement to directly incorporate the logic that populated the temporary table. This means performing the subquery or join directly within the view definition.

DROP VIEW IF EXISTS your_view_name;
CREATE VIEW your_view_name AS
SELECT u.user_id, COUNT(ul.login_id) AS login_count
FROM users u
LEFT JOIN user_logins ul ON u.user_id = ul.user_id
GROUP BY u.user_id;

4

Test the recreated view to ensure it produces the expected results and that the error is resolved.

SELECT * FROM your_view_name LIMIT 10;

2. Remove the View and Recreate in Correct Order easy

If the temporary table is created within a stored procedure or script, ensure the view is created *after* the temporary table is no longer needed or is dropped.

1

Locate the script or stored procedure that creates both the temporary table and the view.

N/A (This requires code review of your existing scripts/procedures)

2

Modify the script/procedure to ensure the view is created *only after* the temporary table has been dropped or is out of scope. If the temporary table is used for intermediate calculations within a larger process, the view should be defined based on the final, persistent data.

Example of a corrected order:

-- Create temporary table (if needed for intermediate steps)
CREATE TEMPORARY TABLE temp_data (...);

-- Perform operations using temp_data...

-- Drop temporary table once no longer needed
DROP TEMPORARY TABLE IF EXISTS temp_data;

-- Create the view based on permanent tables
CREATE VIEW your_view_name AS
SELECT ... FROM permanent_table1 JOIN permanent_table2 ...;

-- Further operations...

3

Execute the modified script or stored procedure.

CALL your_stored_procedure(); or RUN your_script.sql;

3. Use a Derived Table (Subquery) Instead of a View easy

Replace the view with a derived table (subquery) directly in your application queries.

1

Identify the view name that is causing the error.

your_view_name

2

Retrieve the definition of the view using `SHOW CREATE VIEW`.

SHOW CREATE VIEW your_view_name;

3

In your application's SQL queries, replace all references to `your_view_name` with the `SELECT` statement from the view's definition. This effectively makes the view's logic a part of the query itself, avoiding the need for a persistent view object that references a temporary table.

Instead of:
SELECT * FROM your_view_name WHERE ...;

Use:
SELECT * FROM (
    -- The SELECT statement from your_view_name definition
    SELECT col1, col2 FROM base_table WHERE some_condition
) AS derived_table_alias WHERE ...;

🔗

Related Errors

5 related errors

MySQL Error 1352: View References Temporary Table