How can I insert values into a table, using a subquery with more than one result?

sql insert subquery
Futuretec picture Futuretec · Mar 13, 2012 · Viewed 171.2k times · Source

I really would appreciate your help.

Probably it's a quite simple problem to solve - but I'm not the one .. ;-)

I have two tables in SQL Server:

  1. article
  2. prices

Now I want to select a certain set of ids and insert some entries into the prices-table with those ID.

e.g. (wrong and not working SQL)

INSERT INTO prices (group, id, price) 
VALUES (7, (select articleId from article WHERE name LIKE 'ABC%'), 1.50);

SQL Error -> subquery has more than 1 value

thanks for help

Answer

Mike Ryan picture Mike Ryan · Mar 13, 2012

You want:

insert into prices (group, id, price)
select 
    7, articleId, 1.50
from article where name like 'ABC%';

where you just hardcode the constant fields.

Related questions

PostgreSQL: Sub-select inside insert

I have a table called map_tags: map_id | map_license | map_desc And another table (widgets) whose records contains a foreign key reference (1 to 1) to a map_tags record: widget_id | map_id | widget_name Given the constraint that …

Read More
Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More
Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of MyTable(KEY, datafield1, datafield2...). Often I want to either update an existing record, or insert a new record if it doesn't exist. Essentially: IF (key exists) run update command ELSE run insert command What's the …

Read More