0

I want to insert somewhere around 400 rows into a table. All of the column values will be the same fixed value for most of the columns, with exception to one column which I want to pull from a fairly complicated subquery that will pull over 400 unique values from another table using a series of joins and where statements. We attempted the following query:

INSERT INTO mytable (col1, col2, col3, col4)
VALUES(val1, (subquery), val2, val3)

It stated, that it wouldn't work because the subquery yielded more than one result. The only values being pulled from a different table is in the subquery. I cannot insert the subquery into the respective column by itself, since col1 is a NOT NULL column.

  • Is the question for SQL-Server or some other DBMS? – ypercubeᵀᴹ May 10 '15 at 2:15
  • The database is telling you the problem. If your subquery yields more than one row, how can the database insert it in a single row ? You should specify a query that yields only one row for each inserted row. – DeadEye May 10 '15 at 8:18
3

If the (subquery) has SELECT (whatever expression) AS col FROM ..., then you can do:

INSERT INTO mytable 
  (col1, col2, col3, col4) 
SELECT 
  val1, s.col, val2, val3
FROM 
  (subquery) AS s ;

or:

WITH s (col) AS
  (subquery)
INSERT INTO mytable 
  (col1, col2, col3, col4) 
SELECT 
  val1, s.col, val2, val3
FROM 
  s ;
  • That worked perfectly for what I wanted to do. I'm new to database management so your info will help me out on a lot of the stuff I need to do. – jdillman May 10 '15 at 23:49
3

Simple , just try to adapt the following code

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.