?
Solved

SQL problem with insert

Posted on 2014-03-11
4
Medium Priority
?
339 Views
Last Modified: 2014-03-21
I am trying to use the sql command below to duplicate subcategories from product list 1 to product list 2. The select statement returns the expected results but when I try to use it as a subquery it fails. Can anyone tell me how I can make this work?

INSERT INTO slprodsubcat
     (prodlist, catcode, subcatcode, subcatdesc)
  SELECT '2' as prodlist, catcode, subcatcode, subcatdesc
    FROM slprodsubcat
	WHERE prodlist = '1'

Open in new window

0
Comment
Question by:J C
  • 2
4 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39922272
What happens when it fails? And do you have the code for you subquery?
0
 

Author Comment

by:J C
ID: 39922279
I included the entire command I am running. I am referring to the select statement as the subquery.

Here is the error:

PSC-init
Msg 512, Level 16, State 1, Procedure _TI_slprodsubcat, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 1000 total points
ID: 39922284
A select for an insert is not a sub-query. A sub-query is a select within a select.

It would appear from that error message that a trigger is running when you insert data into the table, (maybe called _TI_slprodsubcat otherwise that is an SP being run). And within that trigger a sub-query is returning multiple results as the error says.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 39922477
If there is a TRIGGER, my guess would be someone wrote it thinking that it fires once per row, rather than once per statement, hence the error.  In other words, the TRIGGER has to be re-written correctly to support multiple INSERTs.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question