Search code examples
mariadbtemp-tables

MariaDB: How can I select a temporary table if there is a persistent table with the same name?

I created a persistent table temp and a temporary table temp, that is, both have the same name. How can I use select/update/insert specifically to the persistent or the temporary table? How can I differ between them?

MariaDB Tutorial says:

Note − Temporary tables are permitted to have the same name as an existing non-temporary table because MariaDB views it as a difference reference.

So, I suppose it should be possible to refer to one of these tables. This question is related to this question I posed in SO, but goes one step back.

Solution

  • In case a temporary table has the same name as an existing non temporary table the temporary table will shadow the name of a non temporary table.

    That means in a SQL statement you will not be able to reference the non temporary table.

    A work around would be, to create a view on a non temporary table before creating the temporary table, since the view internally keeps the reference to the non temporary table:

    CREATE TABLE t1 (a VARCHAR(100));
INSERT INTO t1 VALUES ("foo");
CREATE VIEW v_t1 AS SELECT a FROM t1;
CREATE TEMPORARY TABLE t1 (b VARCHAR(100));
INSERT INTO t1 VALUES ("bar");
SELECT * FROM v_t1;
SELECT * FROM t1;