Question 381918 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Is it possible to use some sort of a temporary table with a lifespan of a query?

I have the following table that I generate with a certain query:

+-------------+------------------+
| error_codes | number_of_orders |
+-------------+------------------+
| 2066        |              104 |
| 5092        |              642 |
+-------------+------------------+

I’d like to append the corresponding message for each and every error. The error message table does not exist but I hope it’s possible to create a temporary one within a query which will be destroyed as soon as the query is complete.

+-------------+------------------+
| error_codes | error_message    |
+-------------+------------------+
| 2066        |         Tralalala|
| 5092        |         Ohje     |
+-------------+------------------+

Given the uniqueness of the error codes it can be used as the index to join the tables on. But the point is that I don’t want this table in the DB, it should be a virtual table or something of the sort.

The end result is expected to look as follows:

+--------------------------------+--------------+
| error_codes | number_of_orders | error_message|                                                                          
+-------------+------------------+--------------+
| 2066        |              104 | Tralalala    |
| 5092        |              642 | Ohje         |
+-------------+------------------+--------------+
Tags:

2

Answers


  1. 0

    Did you try using a Common Table Expression (CTE) in your query to create a virtual table containing the error messages ? Check this link

    A simple usage example :

    WITH ErrorMessages AS (
    SELECT 2066 AS error_codes, 'Tralalala' AS error_message
    UNION ALL
    SELECT 5092 AS error_codes, 'Ohje' AS error_message
)
SELECT 
    e.error_codes, 
    e.number_of_orders,
    em.error_message
FROM 
    your_table_name e
JOIN 
    ErrorMessages em ON e.error_codes = em.error_codes;
    Login or Signup to reply.
  2. 0

    I upvoted the other answer suggesting using a CTE, but here’s another solution:

    SELECT 
    e.error_codes, 
    e.number_of_orders,
    em.error_message
FROM 
    your_table_name e
NATURAL JOIN (
    VALUES ROW(2066, 'Tralalala'), ROW(5092, 'Ohje')
) AS em(error_codes, error_message);

    This uses the VALUES statement which is a new feature in MySQL 8.0.

    This is also a rare example of using NATURAL JOIN.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search