Can I add a new auto-increment column to this UNION?

user_12429790 0
Viewed 360

I’m trying to join two tables getting all the data from both tables.

I managed to UNION both tables, but I need to add an ID with auto-increment as the primary key for the new table that I’m creating.
I don't know how to do it and can’t find a way to add it to the query.

CREATE TABLE NEWTABLE
SELECT T1.TEXT as TEXT
       [...]
       FROM TABLE1 T1
       LEFT JOIN TABLE2 T2
            on T1.TEXT = T2.TEXT
UNION
SELECT T2.TEXT as TEXT
       FROM TABLE1 T1
       [...]
       RIGHT JOIN TABLE2 T2
            on T1.TEXT = T2.TEXT
auto-increment mysql
1 Answers

You need to put the column definitions in the CREATE TABLE statement to add the id column. Then provide NULL as the values for it in the SELECT queries.

CREATE TABLE NEWTABLE (
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    text TEXT,
    [...]
) AS
SELECT null, T1.TEXT
       [...]
FROM TABLE1 T1
LEFT JOIN TABLE2 T2
    on T1.TEXT = T2.TEXT
UNION
SELECT null, T2.TEXT as TEXT
FROM TABLE1 T1
[...]
RIGHT JOIN TABLE2 T2
    on T1.TEXT = T2.TEXT
user_14918950
Related