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
Result: Subquery returned more than 1 value
What am I doing wrong?
Thanks in advance,
Frans
Any help would be appreciated.
Thanks.
Steve
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
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?
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...
View 1 Replies View RelatedI 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
View 3 Replies View RelatedHi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
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
********************************
SERVER02:
-----------------------------------------------------
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.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
I am executing the following statement in SQL 2005 and it fails with:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
update #tmp_rpt
set [value] = (select sum([value]) from #tmp_rpt a where a.project_id = #tmp_rpt.project_id and a.sum_date < = #tmp_rpt.sum_date)
from #tmp_rpt
I am tryng to perform a running total.
This doesn't appear to be an aggregate in another aggregate or subquery.
Thanks,
Greg
I am getting an error as
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.
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
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
Hi,
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')
Thanks
Jacquues
Hello
I am new to this forum and pretty new to running queries in SQL Server. I have been doing it for years on an iSeries platform and the following update statement would definitely work in SQL/400....but it does not in SQL Server 2000. Any help would be appreciated.
UPDATE TESTDTA.F0101 X
SET (ABAC07, ABAC12, ABAC28) =
(SELECT AIAC07, AIAC12, AIAC28 FROM TESTDTA.F03012
WHERE AIAN8 = X.ABAN8)
WHERE EXISTS(SELECT AIAC07 FROM TESTDTA.F03012
WHERE AIAN8 = X.ABAN8)
...and here are the errors
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'X'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHERE'.
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.
SELECT substring(sc.container_id,0,14) container_id,
imc.item_class,
sh.ship_to, sh.shipment_id,
cust.ADDRESS1,
sh.carrier,
[code]...
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
What i need as a output
Username | Application | Role
"user1" | "App1" | "Role1"
"user1" | "App1" | "Role2"
"user1" | "App2" | "Role1"
"user1" | "App3" | "Role1"
"user2" | "App1" | "Role1"
"user2" | "App3" | "Role1"
etc.
Hi
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".
Please help me.
Thanks
Mark
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.
Okay, this is what I have as my store procedure:
DECLARE @parentPage varchar(250)
-- Insert statements for procedure here
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))
Please Help! Thanks...
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!
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
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?
I'm getting the message
"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) +
[Code] .....
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
Any pointers much appreciated.
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!
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;
[code]....
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'
Hi,
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);
Thanks and Regards
Salil
Hi Everyone,
I want to insert a row in a table with 2 fields.
The data for the first column comes from a variable
and the data for the second column comes from a select query
can i insert the record in one sql statement like
************************************************** *************
declare @var1 int
set @var1 = 5
insert into mytable1 (field1,field2)
values
(@var1,select field1 from mytable2 where field2 = 10)
************************************************** ***************
where field2 in mytable2 is a primary key.
Awaiting a reply!
Thanks,
saad.
I have to write a delete statement that deletes all customers that have not put in an order I must use a subquery in the exist operator.
View 5 Replies View RelatedHi all, first, let me preface this by saying that I am very new to sqlserver, coming from oracle.Here is my problem: I would like to have a case statement (similar todecode in oracle) that will test a query for a null value. Here is mystatement:SELECTCASE(SELECT MAX(SEQ) + 1FROM [TRANSACTION]) WHEN NULLTHEN 0ELSE(SELECT MAX(SEQ) + 1FROM [TRANSACTION]) ENDIt functions correctly if there is a value for MAX(SEQ) + 1, otherwiseit returns null. It's as if the test for null fails. I hope thatmakes sense.This will ultimately be in an insert statement; I have taken it to thebare minimum to better understand where the problem is.Please let me know if I am doing something dumb. Does this sort ofthing have to in a stored procedure?Anyway, thanks for any help. have a great day!Ryan
View 6 Replies View RelatedHello!
I can use querys like these in Access:
SELECT Field1,
(SELECT Field2 FROM Table2 WHERE Key=1) AS Field2 FROM Table1
SELECT Field1,
(SELECT Count(Field2) FROM Table2 ) AS Field2 FROM Table1
But when I
try execute it with SQL Server Everywhere it says "Token in error
=
SELECT".
Is there some kind of limitations to do this with SQL Everywhere? SQL Everywhere seems to be nice compared with Access and JET but for my project it's useless if I can't use subquerys.
-Teemu