Solved

SQL problem with insert

Posted on 2014-03-11
4
316 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 250 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 250 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

828 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