I'm trying to update column based upon the results of a subquery. I'm getting the error that my Subquery returns more than one result. I've tried adding the EXISTS or IN keywords and cannot get the syntax right. I can't find any examples of how to write this with an Update query.
Here's my query:
UPDATE temp_UpdateRemainingHours SET UsedHours =
(SELECT dbo.vw_SumEnteredHours_byCHARGE_CD.SumEnteredHours, dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD FROM dbo.vw_SumEnteredHours_byCHARGE_CD INNER JOIN dbo.temp_UpdateRemainingHours ON dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD)
FROM dbo.vw_SumEnteredHours_byCHARGE_CD, temp_UpdateRemainingHours
WHERE dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD
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
I have a problem where my subquery is returning two values and I need it to return only one. I believe it is doing this because of the group by DocType clause as there are two DocTypes.
Is there anyway I can sum these values in the subquery, to return only one value. This way the query should run ok.
Thanks for any help you might be able to give,
robinsql
SELECT OCRD.Cardcode, OCRD.cardname, @YearStart AS YearStart, @YearEnd AS YearEnd, @PeriodStart AS PeriodStart, @PeriodEnd AS PeriodEnd, OCRD.County, (SELECT CASE WHEN OINV.DocType = 'I' AND SUM(INV1.Quantity) <>0 THEN COALESCE(SUM(INV1.Price * INV1.Quantity), 0) ELSE COALESCE(SUM(INV1.Price), 0) END FROM INV1, OINV WHERE INV1.DocEntry = OINV.DocEntry AND OINV.CardCode = OCRD.CardCode AND OINV.DocDate >= @YearStart AND OINV.DocDate <= @YearEnd GROUP BY OINV.DocType) AS YTDInvoice, FROM OCRD, OINV WHERE CardType='C'
SQL (using 2012) .I'm trying to create some stored procedures that calculate stats needed for item response theory and test scoring (things like reliability, correlation, etc.). I'm writing a procedure that's aimed at splitting a test in half (first half of the items and second half of the items) and calculating each individual (TestID) score for each half. I can insert the first half of the test, but end up getting an error stating that the subquery returned more than one value. I know it's being caused by the group by clause, but if I remove it I end up with a total count of the items answered correctly, but it's necessary for me to link it batch to the TestID (so each row should have a BatchID [same for all at the moment], a unique TestId, a corresponding FirstHalfScore and a corresponding SecondHalfScore for each testID).
Insert into SplitHalfTestScores (BatchId, TestID, FirstHalfScore) (Select distinct 10001 as BatchId, irt.TestID, count(*) as FirstHalfScore From irt_TestItems irt where batchID=10001 and ItemId <=40 and TestId = irt.TestId and AnsweredCorrectly = 1 group by TestId)
Update SplitHalfTestScores Set SecondHalfScore = (Select count(*) From irt_TestItems irt where BatchId = 10001 and TestId = irt.TestId and irt.ItemId > 40 and AnsweredCorrectly = 1 group by TestId)
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1) In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment.  If I do change the query to something below then definately it will break - Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment ( DeptId int identity(1,1) primary key, name varchar(50) ) create table ChildDepartment ( Id int identity(1,1) primary key,
Table A, Table B. need to update table A balance field from table b sum of amount
UPDATE CUSTOMERS SET BALANCE=(SELECT SUM(AMOUNT) FROM PAYMENT,CUSTOMERS
WHERE CUSTOMERS.ID=PAYMENT.ID GROUP BY PAYMENT.ID)
Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE ( OuterID int IDENTITY (1, 1) NOT NULL, MethodID int NULL, CompID int NULL, FormID int NULL, InnerID int NULL )
-- UPDATE Outer Table with best match from Inner table UPDATE @Outer SET InnerID = IJ.InnerID FROM @Outer OJ INNER JOIN ( SELECT TOP 1 I.* FROM @Inner I WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID AND IsNull(I.CompID, OJ.CompID) = OJ.CompID AND IsNull(I.FormID, OJ.FormID) = OJ.FormID ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC ) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID) AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID) AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5. Can anyone help me? Thanks in advance.
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane... (0 row(s) affected) (1 row(s) affected) How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect) (1 row(s) affected) If anyone has any ideas where to look next, I'd appreciate it. Thanks
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100 SELECT * FROM Customer WHERE CountyId IN ( SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId) THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
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?
I'm bothered by an issue of updating a column in input dataset from a update query. It looks like SSIS has a very poor function on this.
Example, I have an input dataset of name, salary, dept_no, and I have another table called departments which has fields of dept_no, Dept_name, basic_salary
now I want to update salary column in input dataset with basic_salary if it's salary is smaller than the basic_salary.
update #tmp set salary = basic_salary where #tmp.salary <departments.basic_salary and #tmp.dept_no = departments.dept_no
how could I impletement this in SSIS package?
I tried with lookup, modify scripts by enabling memory restriction. It doesn't say any error when I save the package, but I never get pass debug.
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
in my original database have a column which is for "path" ,the record in this column is like → �mms://192.12.34.56/2/1/kbe-1a1.wmv】 this kind of column is about 1202,045 .. I don't think is a easy job to update by person.. it may work but have to do same job 1202,045 times..
I have to change � mms://192.12.34.56/2/1/kbe-1a1.wav】 to � mms://202.11.34.56/2/1/kbe-1a1.wav】 I tried to find the reference book and internet . can't find out the answer for this problem. can you help? or maybe is it a impossible job? thanks
Hi All, When I use the following I get an error. I think it is because of duplicate records in my table.
Update person Set username = (Select username From update_person Where person.firsname = update_person.firstname and person.lastname = update_person.lastname)
There are a few users that have the same first and last name. How can I ignore the duplicate records and continue to update the table?
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
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 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?
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'.
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
I want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True. If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null App1 ||| Linux |||UDB App1 ||| AIX |||UDB App1 ||| Linux |||Sql
Why don't i ever get return value of 1 when the following binary column (profSignature) is null? RETURN SELECT ISNULL(profSignature, profSignature) FROM mpProfiles WHERE ApplicantID = CAST(@CID AS INT) AND ProfileID = CAST(@PID AS INT)
Can anyone provide me with some SQL that will identify rows from a table where a varchar column named "Notes" contain Carriage Returns?
I know that with report writer SQR I can translate CR's to white space but I do not know of any Sybase function that will allow me to do the same, any ideas on this well would be appreciated.
Hi folks,I have an issue with a column I created in my query called Instance.SELECT Object_Id, Event_type, Audience, Subject, Provider, Academic_Year, Start_date, End_date, CONVERT(varchar, Academic_Year) + ' (' + CONVERT(varchar, Start_date, 103) + ') : ' + Event_type AS InstanceFROM EventsORDER BY Event_type Above is my query. The problem is because the start date column can be null, it also returns the Instance column as null for that row.I thought it would have just missed out the date and display the rest, but it doesn't.Is there any way I could get the Instance column to display a value, when the start date is null?ThanksEdit: Managed to sort it using ISNULL()