Search code examples
mysqlinsertsql-insertinsert-into

Multiple INSERT with SELECT statement and SELECT FROM WHERE simultaneously

I have a table shaped like following :

CREATE TABLE Cars (
  id integer PRIMARY KEY AUTO_INCREMENT NOT NULL,
  brand text,
  color text
);

You can see the database by following this SQLFiddle link.

You see on the right panel the query I tryied to use in order to insert values using SELECT statement.

QUESTION

Can someone point me in the right direction to insert values with SELECT statement with and without WHERE clause simultaneously ?

I tryied the following insert to come by this issue :

INSERT INTO Cars (brand, color)
SELECT "mazda", "black",
SELECT Cars.brand, Cars.color FROM Cars WHERE Cars.brand = "ferrari";
Solution

  • Use UNION ALL:

    INSERT INTO Cars (brand, color)
SELECT "mazda", "black"
UNION ALL
SELECT Cars.brand, Cars.color FROM Cars WHERE Cars.brand = "ferrari";

    Demo here