CODEWITHSUNDEEP
sqlinsertsubquery
Futuretec
Futuretec

Reputation: 1481

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

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

Upvotes: 147

Views: 273997

Answers (7)

user12829430
user12829430

Reputation:

i am using this method.

WITH selected AS (
   SELECT articleId FROM article WHERE name LIKE 'ABC%'
) INSERT INTO prices(group, id, price)
  SELECT 7, articleId, 1.50 from selected;

Upvotes: 5

Stefan H
Stefan H

Reputation: 6683

Try this:

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

Upvotes: 33

Taryn
Taryn

Reputation: 247690

If you are inserting one record into your table, you can do 

INSERT INTO yourTable 
VALUES(value1, value2)

But since you want to insert more than one record, you can use a SELECT FROM in your SQL statement.

so you will want to do this:

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

Upvotes: 18

Teja
Teja

Reputation: 13534

INSERT INTO prices(group, id, price)
SELECT 7, articleId, 1.50
FROM article where name like 'ABC%';

Upvotes: 1

Muhannad A.Alhariri
Muhannad A.Alhariri

Reputation: 3912

the sub query looks like

 insert into table_name (col1,col2,....) values (select col1,col2,... FROM table_2 ...)

hope this help

Upvotes: 5

Terkel
Terkel

Reputation: 1575

INSERT INTO prices (group, id, price)
  SELECT 7, articleId, 1.50 FROM article WHERE name LIKE 'ABC%'

Upvotes: 14

Mike Ryan
Mike Ryan

Reputation: 4374

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.

Upvotes: 227

Related Questions