How would I insert the same row with all the different subquery values at the same time, almost like an iteration over the subquery?

CREATE TABLE messages (messageid INTEGER PRIMARY KEY
                       , message VARCHAR(150)
                       , sender VARCHAR(40)
                       , recipient VARCHAR(40))

CREATE TABLE employees (employeeid INTEGER PRIMARY KEY
                        , employeename VARCHAR(40)
                        , area VARCHAR(40))

My problem is that I don’t know a simple way of inserting a selection of employees from the employees table directly into the messages table like:

INSERT INTO messages (message
                      , sender
                      , recipient) 
VALUES ("Message."
        , "System"
        , (SELECT employeename 
           FROM employees 
           WHERE area = "Maintenance"))

This returns the error: Error Code: 1242. Subquery returns more than 1 row and I’m not sure on how to proceed from here.

Answer

Use insert . . . select:

INSERT INTO messages (message, sender, recipient)
    SELECT 'Message.', 'System', employeename
    FROM employees
    WHERE area = 'Maintenance';

Note that the values keyword is not needed. I also replaced the double quotes with single quotes, because these are the SQL standard delimiter for strings.

Advertisement

Answer