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

user_12926823 0
Viewed 44
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.

mysql sql
2 Answers

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.

user_11440350

You can not insert into the table messages without inserting value for column messageid even if you change the double quotes with single quotes because "Field 'messageid' doesn't have a default value". You need to do it like this:

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

Here is a small demo

Other option is to create your table messages like this:

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

Then your code would work:

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

Here is a demo showing you that example

But your error Subquery returns more than 1 row is caused because you have two rows in your table employees with value "Maintenance" in the column area like you can see in this small demo: click here to see what causes your error

Now what you need to do is to decide what will you do with this situation. Do you want to insert max or min value from the table employees like this:

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

Or maybe you want to add one more criteria to the select statement like this:

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

Both of this examples work, check it here, but it is up to you what do you want to insert.

user_65650380
Related