CODEWITHSUNDEEP
mysqlselectinsert
Lera
Lera

Reputation: 129

insert... select with only some fields

I have two tables in my database and I want to find all records that exist in the first table but not the second. Then I want to copy the id and name (but not the rest of the fields) from the first table into the second, and set a value for a third column in the second table. I'm using this to get the records:

SELECT table1.* FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE
table2.id = table1.id)

but I'm not sure of the proper syntax for the INSERT command. I think it would start

INSERT INTO table2 [id,name]

but what do I specify after that?

Upvotes: 1

Views: 4169

Answers (1)

StudyOfCrying
StudyOfCrying

Reputation: 530

Like this:

INSERT INTO table2 (col1, col2, col3) VALUES (1, 2, 3);

If you're doing the insert from the select, it will look like this:

INSERT INTO table2 (id, name, thirdCol)
SELECT (table1.id, table1.name, 'Whatever value') FROM table1
WHERE NOT EXISTS (SELECT id FROM table2 WHERE table2.id = table1.id);

Upvotes: 4

Related Questions