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

MySQL can values statement columns be named?

I have searched on the forums and failed to find how can one name columns for a values statement subquery.

Here is my query in MySQL:

SELECT *
FROM (VALUES
    ('PPA', 'blah', 30)
    , ('PPB', 'blah blah', 60)
) AS X

Here is what I can do in T-SQL:

SELECT x.a, x.b, x.c
FROM (VALUES
    ('PPA', 'blah', 30)
    , ('PPB', 'blah blah', 60)
) AS x(a, b, c)

Very simple, yet can’t find out.

Note: As I know this query can be simplified to no subquery, my question remains as this will be merged into another query for an insert and a left join of mine.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    From what I could find further on looking at MariaDB, you could use common table expressions as such:

    WITH t(a, b, c) AS (
    VALUES
    ROW('PPA', 'blah', 60)
    , ROW('PPB', 'blah blah', 60)
)
SELECT t.a, t.b, t.c
FROM t

    from source


  2. 0

    You are almost there just need to add ROW.

    SELECT * FROM (VALUES
    ROW('PPA', 'blah', 30), 
    ROW('PPB', 'blah blah', 60)
) AS X (a, b, c);

    Result:

    enter image description here

    More about ROW you can read here and here.

    Login or Signup to reply.
  3. 0

    You could use a union as well

    SELECT 'PPA' AS a, 'blah' AS b, 30 AS c
UNION ALL
SELECT 'PPB' AS a, 'blah blah' AS b, 30 AS c
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search