Search code examples
mysqlsubquerybulkinsertmultiple-records

Multiple Row Inserion via subQuery

i am trying to insert multiple rows using a subquery but it gives and error "SubQuery Returns more than 1 rows"

scenario is that i want to add comment against every test of a subdepartment, i am getting all test id's via subquery but i not being able to iterate over id's and insert comment against each test. Here is my SQL Query

INSERT INTO dc_tp_comment  (labid,branchid,Comment,testid,lastupdated,enteredby)
Values('abcd',101,'comment here',(select T.testid
from dc_tp_test T
Inner Join dc_tp_subdepartments S
on T.subdepartmentid = S.subdepartmentid
Where S.subdepartmentid = 13),sysdate(),1)
Solution

  • You cannot use subselect in just one column, use them for whole row:

    INSERT INTO dc_tp_comment  (labid,branchid,Comment,testid,lastupdated,enteredby)
select 'abcd',101,'comment here', T.testid, sysdate() , 1
from dc_tp_test T Inner Join dc_tp_subdepartments S
on T.subdepartmentid = S.subdepartmentid
Where S.subdepartmentid = 13