0

I need to insert mutliple tuples into a table using a subquery as one column value and a static value as the second column value. How would I go about doing this?

My SQL knowledge isn't very vast so I was wondering if you guys could clear this up for me.

Here is an invalid query that shows what I'm trying to achieve.

INSERT INTO deleted_messages (message_id, account_id) VALUES ((
SELECT message_id FROM messages 
WHERE conversation_id = 23
), 42);

The subquery returns a table with multiple "message_id" values (which is what I want).

The results I'm looking for:

Table: deleted_messages
id | message_id | account_id
-------------------------
 1 | 25         | 42
 2 | 36         | 42
 3 | 94         | 42

Let me know if I need to clarify. Thanks in advance!

pajamas
  • 110
  • 6
  • Possible duplicate of [SQL Insert into ... values ( SELECT ... FROM ... )](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) – Ezequiel Moreno Dec 29 '15 at 23:05

2 Answers2

3

Just use insert . . . select, no values:

INSERT INTO deleted_messages (message_id, account_id) 
    SELECT message_id, 42
    FROM messages 
    WHERE conversation_id = 23;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you! That's exactly what I needed. I'll go ahead and brush up on SQL inserts haha. I'll accept this as the answer when I can. – pajamas Dec 29 '15 at 23:04
0

You need to select your static value from within the subquery as well, like this:

INSERT INTO deleted_messages (message_id, account_id) 
SELECT message_id, 42 FROM messages 
WHERE conversation_id = 23;
ArSeN
  • 5,133
  • 3
  • 19
  • 26