Subquery Returned More Than 1 Value. Error On Update SQL
Sep 27, 2004
Hi guys,
I am just trying to do a simple update statement and am getting this error
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.
My statment is like this:
Code:
update [Clients]
SET [OPT-OUT] = 0 WHERE [OPT-OUT] is null
I have used this against another table with success, but get the error against that one. Anyone have any idea?
The error below is encounter when a mass update is attempted on our employee table.
Server: Msg 512, Level 16, State 1, Procedure emp_lock_id, Line 8 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.
The Trigger code is below
CREATE TRIGGER emp_lock_id ON dbo.EMPLOYEE FOR UPDATE AS DECLARE @NEXT_NUM DECIMAL(17, 12) DECLARE @NEW_NUM DECIMAL(17, 9) DECLARE @OLD_LOCK_ID DECIMAL DECLARE @NEW_LOCK_ID DECIMAL SET @OLD_LOCK_ID = (SELECT EMP_LOCK_ID FROM DELETED) SET @NEW_LOCK_ID = (SELECT EMP_LOCK_ID FROM INSERTED) IF @OLD_LOCK_ID <> @NEW_LOCK_ID BEGIN RAISERROR('Record Modified by different user.', 16, 1) ROLLBACK TRANSACTION END ELSE BEGIN SET @NEXT_NUM = (convert(decimal(17,12), getdate())) SET @NEW_NUM = @NEXT_NUM * 1000 UPDATE EMPLOYEE SET EMP_LOCK_ID = (@NEW_NUM - floor(@NEW_NUM)) * 1000000000 FROM EMPLOYEE, INSERTED WHERE EMPLOYEE.EMP_EMPLOYEE_ID = INSERTED.EMP_EMPLOYEE_ID END
I am struggling with syntax for an update statement. Goal: If a RelationID has invoice history then HasHistory=1
UPDATE [Tbl_Relations] SET [HasHistory]= 1 FROM [Tbl_Relations] r INNER JOIN (SELECT RelationID FROM vw_Invoice_history GROUP BY RelationID ) inv ON r.RelationID= inv.RelationID
Hi i am a newbie in sql so any help would be appreciated. i have a serverlog table that initially had one server and different functions types, my sql query got a percentage of the execution times for the different functions and all worked well, however as soon as i put more servers in to the table i got the following error...
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Here is my SQL, the problem is with the sub queries returning more than one server type... Hope somebody can help and thanks in advance..
SELECT server, [function], prelimquery * 100 AS [%OverallProcessingTime] FROM (SELECT server, [function], subquerya / (SELECT subqueryb FROM (SELECT totalallreqexec * (SELECT AVG([exec_time]) FROM llserverlogs) AS subqueryb FROM (SELECT COUNT(*) AS totalallreqexec FROM llserverlogs) DERIVEDTBL) DERIVEDTBL) AS prelimquery FROM (SELECT server, [function], NoRequestExecutions * (SELECT avgexectime FROM (SELECT DISTINCT server, AVG(exec_time) AS avgexectime FROM llserverlogs GROUP BY server) DERIVEDTBL) AS subquerya FROM (SELECT DISTINCT server, [function], COUNT(*) AS NoRequestExecutions FROM llserverlogs GROUP BY server, [function]) DERIVEDTBL) DERIVEDTBL) DERIVEDTBL
Hi group I get the above error in my SP when the tblHolidayDates has more than one record. Here is my SP: begin
if datediff(day,getdate()-10 ,(Select HolidayDate from tblHolidayDates))= 3 AND DATEPART(dw,(select holidaydate from tblHolidayDates)) = 1 -- Monday Date SELECT su.SuspensionID, su.ConsentNo, si.SiteID, si.NameNo + ' ' + si.Address1 AS SiteAddress, si.SiteDesc, si.SuspensionStart, si.SuspensionEnd, si.Address1, si.BaysAttached, rd.RefValue AS NoticeDays, si.MergeID FROM Suspension su INNER JOIN SiteData si ON su.SuspensionID = si.SuspensionID INNER JOIN RefData rd ON si.NotReq = rd.RefCode WHERE su.Status = 'ACT' and si.Archived = 'N' and (datediff(day, getdate()-10 , si.SuspensionStart) = rd.RefValue OR (datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue + 2) or datediff(day,(Select holidaydate from tblHolidayDates), si.SuspensionStart)=rd.RefValue + 1) end
I have some code that calls a stored procedure on SQL Server 2005 using the Microsoft JDBC driver 1.1. The code normally works however, every once in a while an exception is thrown:
Code Snippet
com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression.
Generally, this has been resolved by restarting SQL Server 2005, but why is it showing up to being with?
Stored Procedure:
Code Snippet
ALTER Procedure [dbo].[addRecord] @userID int, @itemID int, @info varchar(50), @comment varchar(50), @output int output AS
Declare @dateSubmitted datetime set @dateSubmitted = getDate();
--Insert the new record. THIS TABLE has an ID identity Primary Key --column that auto-increments. insert into RecordTable (UserPerson, Information, DateSubmitted) values (@userID, @info, @dateSubmitted);
---Get the ID Assigned in the record table. The Item table ---has a Foreign key on this column. Declare @assignedID int set @assignedID = (select ID from RecordTable where DateSubmitted = @dateSubmitted)
/**Set the output parameter.*/ set @output = @assignedID;
--Now update the Item Table. insert into Item (ID, RecordID, Comment) values (@item,@assignedID, @comment);
Hi, I am using .NET sqlBulkCopy to insert several rows at once into my sql table. Yesterday I created a simple trigger on this table to take the primary key of the inserted row and place into another table. Fairly straightforward I thought, but I keep getting this error when inserting 2 or more rows (works fine with one row): "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." To test this, I changed the sqlBulkCopy into individual INSERT statements in a for each loop and then it works perfectly, so I know that it's definitely the sqlBulkCopy causing the problem, not the trigger itself. Here is my trigger code anyway: alter trigger tg_InsertClaim on AuditItemfor insertas declare @AuditItemID as intset @AuditItemID = (select i.AuditItemID from inserted i inner join dbo.AuditItem a ON i.AuditItemID = a.AuditItemID) insert into Claim (AuditItemID) VALUES (@AuditItemID) Thanks!
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
I am attempting to Insert multiple rows in a table by passing DataTable to a Stored Proc. My code is the same as the examples on the net, however when I execute the code I receive the Subquery returned more than 1 value error.
I first created up a User-Defined Table Type
My stored proc is as follows:
ALTER PROCEDURE [KCC].[Insert_MaterialJobMtl] @MtlTblMaterialType READONLY AS BEGIN Set NOCOUNT ON;
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, 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.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar, RDR1.U_Variety, (SELECT OITM.U_Variety FROM OWOR INNER JOIN WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN OITM INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity, OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet, OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode FROM OWOR AS t0 INNER JOIN ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode WHERE (t0.Status <> 'L')
can anyone tell me why i get the following error when i run the below query? its a simple query. i dont understand why its throwing an error. Thanks in advance.
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.
update purchaseorders set orderstatusID = 4 where purchaseorderid in ( select distinct postprocesspurchaseorderID from wishlistitems where postprocesspurchaseorderID is not null )
I am getting the below error while trying to run sql query
Msg 512, Level 16, State 1, 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.
but if in the same below query if i put the parameter in upto 10 then its working fine......if the parameter is 11 or above then the query is giving the above error.
i am trying to run Select query to get "application name" and "role" for each user in the DB problem is that each user has more than one App that he is useing and for each App he can have more than one role
My code is:
Code: select top 100 username, (Select b.name from application b inner join userroleapplication c on b.applicationID = c.applicationID ) Application, (select name from role d, userroleapplication gr where gr.roleID = d.roleID and gr.userID = a.userID) role from users a where username in ( 'username' )
i know where is the problem but i cant figure how to fix this
I have two tables and I need write a query that include subquery that return more than 1 value,Can you give me a idea how to write the query
The table with data is like this,one file maybe belong to many group.
Filename FilegroupID
file1 1 file2 2 file1 3 file1 4 file3 1 file2 5
I need write a query to get the result like this
file1 1,3,4 file2 2,5 file3 1
I try to write a query with subquery but I get error message
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
I use SQL Server 2005.but I need the subquery return data like "1,3,4".
I have a simple update query that looks like this:
Update Table1 set midpointDate = (select dateadd(day,(datediff(day,startDate,endDate)/2),starteDate) from Table1) where Table1.RowID = Table1.RowID
I am receiving the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."Yes, it is all contained in the same table.
Okay, I want to fill a table in one database with some info from an identical table from another database. When I run this query, I get the following error:
"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."
But the point of the subquery was to in fact return more than one value.
SET @parentPage = (SELECT [frntPage] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID] AND [OLissue].[frntPage] IS NOT NULL AND [Outlook].[parent] IS NOT NULL))
IF @parentPage IS NOT NULL
IF exists (SELECT [Outlook].[ID], [Outlook].[title], [Outlook].[name], [Outlook].[issueID] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID])) SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT DISTINCT NULL ID, 'No parent page yet' title FROM [Outlook]
And I kept getting this error when testing in SQL Management Studio.
Msg 512, Level 16, State 1, Procedure parentPage, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I'm having problem Inserting data from 1table into another table. I'm getting the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I get the reason why I'm having this error message but I don't know how to fix code... Help please? Thanks
Here's an example of what I'm trying to do...
table1 has: ID | Name | countryID 1 | Joe | 101 2 | Ben | 102 3 | Tom | 102
Now I wrote this code to insert some of the data from table 1 into table2...
insert into Table2(CompName, ID) values('Information',(select ID from table1 where countryid=102))
select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1 I need to write that kind of sql to return me single value a and multiple values b on each of lines, like a b ---------------------------- tom small,big,hugh But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'. Please help me find a solution, thanks!
when I run the query UPDATE dbhal.dbo.tblUser SET vchrFirstName = FirstName, vchrLastName = LastName, vchrLogin = LoginName, bitActive = Active, vchrEmailAddress = EmailAddress FROM TempEmployee, tbluser where EmployeeID = intEmp and vchrcompid = 'sam'
and there is trigger
CREATE TRIGGER UPDATE_tblUser ON tblUser FOR UPDATE AS DECLARE @CompanyId as varchar(20), @intUid as int SET @CompanyId =(Select vchrCompID from deleted) Set @intUid = (Select intUid from deleted) IF @CompanyId = 'sam' BEGIN UPDATE [dbPortal].[dbo].tblUser SET intEmp = (Select intEmp from inserted), vchrCompID = (Select vchrCompID from inserted), vchrPwd = (Select vchrPwd from inserted), vchrLogin = (Select vchrLogin from inserted), vchrFirstName = (Select vchrFirstName from inserted) , vchrLastName = (Select vchrLastName from inserted), vchrEmailAddress = (Select vchrEmailAddress from inserted), bitActive = (Select bitActive from inserted), intPWDAttempt = (Select intPWDAttempt from inserted), vchrCreatedWho = (Select vchrCreatedWho from inserted), dtmCreatedDate = (Select dtmCreatedDate from inserted), vchrModifiedWho = (Select vchrModifiedWho from inserted), dtmModifiedDate = (Select dtmModifiedDate from inserted) WHERE intUID = @intUid END
I get the error. What can I do to get this to run?
"Subquery returned more than 1 value." etc when running a query on a linked server through OpenQuery. This returns around 6 values (some of them are fairly long varchar's). When running another query the same way which returns 2 values, it works fine.
The problem query also works fine when run locally. It's calling a User-Defined Functions which returns a table, but the second query which works fine also does this.
Where can this subquery be ? Inside the UDF (shouldn't matter, should it?) ?
Am getting error:- Subquery returned more than one value,that is not permitted when sub query follows
=,<,>.................................Below is my query declare @WorkOrder as nvarchar(10) Set @WorkOrder =( Select X.PrjCode from OPRJ X Where X.PrjCode='[%1]')
SELECT L.Project ,l.U_PINo as [Indent No], M.DocNum AS 'GRN No', M.DocDate as 'Date', M.CardName as 'Vendor Name',L.ItemCode,l.Dscription,l.Quantity,l.unitMsr as'UOM',l.Price,l.DiscPrcnt ,L.LineTotal, isnull((SELECT (TaxSum) FROM PDN4 where statype=-90 and DocEntry=M.DocEntry and L.LineNum =PDN4.LineNum),0) +
Please try the following, see that trigger is getting invoked, even if there is no row inserted into trigger table.
drop TABLE X GO CREATE TABLE X ( x1 bigint NOT NULL , x2 nvarchar(40) ) GO drop TABLE Y CREATE TABLE Y ( y1 bigint NOT NULL , y2 nvarchar(40) ) GO CREATE TRIGGER trg1 ON X FOR INSERT AS BEGIN DECLARE @prfirststatus INTEGER, @newcontextid NCHAR SELECT @newcontextid = x2, @prfirststatus = x1 FROM inserted PRINT 'See the trigger getting invoked without even a single ' + ' row being inserted in table X and values passed to this ' + ' triggers are inserted.x2 = ' + @newcontextid insert into Y values (@prfirststatus,@newcontextid) END GO insert into X SELECT y1, 'x' from Y where y2 = 'DOESNTEXIST'
I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:
DELETE FROM TEST1 WHERE (ID, ID1) NOT IN (SELECT ID,ID1 FROM TEST2);
I need to update the status_date field in the journal table with the most recent status_date value in the transactions_batch_tmp.status_date column. However, when I run this query I get a syntax error: An aggregate may not appear in the set list of an UPDATE statement.
UPDATE journal SET status_date = max(tmp.status_date) FROM transactions_batch_tmp tmp, contacts c, products p WHERE c.emailaddress = 'test@yahoo.com' AND c.contact_id = j.contact_id AND j.product_code = 'EAENERGY' AND j.product_code = p.product_code
Update Table1 set Table1.Field1 = (Select Field1 from Table2 where Table2.Field2 = Table1.Field2)
SQL Mobile doesnt seen to support this, so i tryed to use FROM and INNER JOIN, but again, it does not worked... Any ideas if is possible to do something like this in SQL Mobile?
Hi everybody,I have a little Problem with an update and Detailsview.I have 2 table (tblUser - uid,uName,rId) and (tblRole - rId, rDescription) In my update I don't want to use the roles id, I want to use the roles Description.Via sql I do a update the role to Admin like that UPDATE tblUser SET rId = (SELECT rId FROM dbo.tblRoles WHERE (rDescription = 'Admin')) But I don't get it to work for the detailsview update
Hi,I have one table name: artcolumn:symbol_art price1 price2----------- ------- -------AG-0001 20 40AG-0001S null nullAG-0002 40 60AG-0002S null null....How paste in null price1 and price2 from oryginal symbol_art AG-0001,AG-0002 ?(duplicate symbol_art %-%'S ' it's always the same for oryginal symbol_art)thanks for any helpTom
