Okay, I’ve been trying to figure out how to do an insert into a table where one column’s value is always the same, but the second value will be from a select statement.
Something like:
INSERT INTO <table_name> (column1, column2)
VALUES ('055', (SELECT DISTINCT <column_name> FROM <source_table> WHERE <various conditions>)
The code above, obviously doesn’t work. I get an error “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”
Never mind. I figured it out.
INSERT INTO <table_name> (column1, column2)
(SELECT DISTINCT <column_name>, '055' FROM <source_table> WHERE <various conditions>)
This seems to work!
Yep. In your first example, you’re attempting to insert a single record, but the select is returning multiple records.
In the second example, you’re inserting a set into your table - one or more records.
Watch out though, if there are triggers on your table. I think the last time I did that sort of operation, the trigger didn’t fire the way I expected. It might be a poorly-constructed trigger though.