MySQL insert data with fixed values and multiple select results

Question

Suppose I have a table structure like below:

notification_table

| id | receiver_id | type | content | time |

The receiver id is from a user table:

user_table

| id | username |

And the content and time are from a broadcast table:

broadcast_table

| id | content | time |

So when I need to insert into this notification table, I need to select id from users and select content+time from broadcasts, and the type is fixed to "system_broadcast". Can I do this in one query?

I tried

INSERT INTO notification_table (receiver_id, type, content, time) 
VALUES (
       (SELECT id FROM user_table WHERE username='test' LIMIT 1), 
        'system_broadcast', 
       (SELECT content, time FROM broadcast_table)
)

But it returns error like "mysql Operand should contain 1 column(s)".

Gordon Linoff · Accepted Answer · 2015-01-11 14:24:15Z

3

Yes, you can do this using insert . . . select. This seems to match the intention of your original query:

INSERT INTO notification_table (receiver_id, type, content, time) 
    SELECT (SELECT id FROM user_table WHERE username = 'test' LIMIT 1), 
           'system_broadcast',
           content, time
    FROM broadcast_table;

Note that this will insert one row for every row in broadcast_table. You might want a where clause or limit to get only particular rows.

answered Jan 11, 2015 at 14:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

mrmoment Over a year ago

Yes, it works. And I do have the limit condition clause which I can handle myself. Thanks.

Collectives™ on Stack Overflow

MySQL insert data with fixed values and multiple select results

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related