INSERT ... VALUES using SELECT query

Question

This is my table :

CREATE TABLE my_table (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL
);

And this is an existing table user :

CREATE TABLE user (
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL
);

Now, I want to INSERT into my_table using values from table user.

Using this query :

INSERT INTO my_table (id, firstname, lastname) 
VALUES (NULL, (SELECT firstname, lastname FROM user) );

I get this error :

Operand should contain 1 column(s)

How to do to insert using select query and auto_increment values?

juergen d · Accepted Answer · 2018-07-12 16:31:51Z

2

INSERT INTO my_table (id, firstname, lastname) 
SELECT NULL, firstname, lastname FROM user

or better

INSERT INTO my_table (firstname, lastname) 
SELECT firstname, lastname FROM user

answered Jul 12, 2018 at 16:31

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nicola Ben · Accepted Answer · 2018-07-12 16:36:15Z

0

Try with

INSERT INTO my_table (firstname, lastname) 
SELECT firstname, lastname FROM user;

Autoincrement will be manage by first table, where you defined it.

answered Jul 12, 2018 at 16:36

Nicola Ben

11.5k9 gold badges46 silver badges71 bronze badges