MySQL Union After Select

Question

Is there any way to insert rows into a temporary table after creating it via SELECT statement in MySQL? I have this query that works as expected:

SELECT goldusers_user_ci.user_id, goldusers_users.username, goldusers_user_ci.contact_value
FROM goldusers_users
INNER JOIN goldusers_user_ci
ON goldusers_users.id = goldusers_user_ci.user_id
ORDER BY user_id

The idea is to select this query as a temporary table and add one row to it, like so:

SELECT * FROM(
SELECT goldusers_user_ci.user_id, goldusers_users.username, goldusers_user_ci.contact_value
FROM goldusers_users
INNER JOIN goldusers_user_ci
ON goldusers_users.id = goldusers_user_ci.user_id
ORDER BY user_id
) AS DBusers
INSERT INTO DBusers(user_id,username)
VALUES(-1,"All")

However, I get a syntax error. Is there any way to do this?

**Edit: I believe I actually need to do a union after the select: ***the issue is the placement of the ORDER BY statement. Moving it to the bottom works

SELECT goldusers_user_ci.user_id, goldusers_users.username, goldusers_user_ci.contact_value
FROM goldusers_users
INNER JOIN goldusers_user_ci
ON goldusers_users.id = goldusers_user_ci.user_id
UNION ALL
SELECT -1 as user_id, "All" as username, "" as contact_value
ORDER BY user_id

The syntax is `INSERT INTO tablename (columns...) SELECT ....` — Barmar, Oct 21 '21 at 19:06
See the documentation for [`INSERT`](https://dev.mysql.com/doc/refman/8.0/en/insert.html) — Barmar, Oct 21 '21 at 19:07
@Barmar Yes, maybe I do not need a temporary table. I am looking to add one row to the query after the select statements. — marksy95, Oct 21 '21 at 19:29
Your first query contains 3 columns, but your added row only has 2 columns. They need to be the same. — Barmar, Oct 21 '21 at 19:30

score 0 · Answer 1 · answered Oct 21 '21 at 19:08

0

How about something like this?

SELECT * 
INTO #TempTable
FROM(
        SELECT goldusers_user_ci.user_id, goldusers_users.username, goldusers_user_ci.contact_value
        FROM goldusers_users
        INNER JOIN goldusers_user_ci
        ON goldusers_users.id = goldusers_user_ci.user_id
        
        UNION ALL
        
        SELECT -1, 'All', ''
) AS DBusers

SELECT *
FROM #TempTable

DROP TABLE #TempTable

answered Oct 21 '21 at 19:08

manderson

837
1
6
18

MySQL doesn't use `#` before temporary table names. – Barmar Oct 21 '21 at 19:09
yeah my bad. Create a table first, replace #TempTable with the table. – manderson Oct 21 '21 at 19:14
MySQL doesn't use `FROM` in `INSERT INTO` statements. – Barmar Oct 21 '21 at 19:15
It seems like you're giving the SQL-Server answer. – Barmar Oct 21 '21 at 19:16

score 0 · Answer 2 · answered Oct 21 '21 at 19:08

0

I cannot test what you want, but base on your query, I think you want to Insert data after select data.

SELECT * FROM(
SELECT goldusers_user_ci.user_id, goldusers_users.username, goldusers_user_ci.contact_value
FROM goldusers_users
INNER JOIN goldusers_user_ci
ON goldusers_users.id = goldusers_user_ci.user_id
ORDER BY user_id
) AS DBusers;
INSERT INTO DBusers(user_id,username)
VALUES(-1,"All");

I think you just need to add semicolon ;

answered Oct 21 '21 at 19:08

AdityaDees

1,022
18
39

The first query above works without the semicolon, but adding the semicolon to the second does not solve the issue. – marksy95 Oct 21 '21 at 19:24
@marksy95 The semicolon is needed to show where the first query ends and the second query starts. – Barmar Oct 21 '21 at 19:31

MySQL Union After Select

2 Answers2