SQL Server :: Update Multiple Records - Subquery Returns More Than 1 Row
Jan 5, 2011
I have a table line (ID, LineNo, LineName) having values as :
1, 1, <blank>
2, 2, <blank>
3, 3, <blank>
ID is PK with Auto-Increment and LinNo(I am manually incrementing through ASP.Net Page while record insertion) Now i have to write a query such that it updates LineName column from line table which is blank(as shown above) with values from table linet(linenamet) having values as :
a
b
c
My trial query is :
update line set LineName = (select linenamet from linet);
It gives ERROR as : Subquery returns more than 1 row
Expected Result : After Updation it should show line table as :
1, 1, a
2, 2, b
3, 3, c
I am running an Update statement against an SQL database on server 2008. The update runs but does not update the record, but returns no errors.The database is part of a commercial help desk package called TrackIt 8.5 which was just a fresh install on a new web server and a new sql server. The update is running from a web form we use to automatically create active directory accounts. After the account is created the web form closes out the work order in the Trackit db. This was working fine when the system was running on Server 2003 and SQL server 2005. Running Trackit version 7.02.The login use to access the database has full permissions to read and write to the database, just as it did on the old server which never had an issue updating. Odd thing is, if I log onto the SQL server and make a change to any single field in that record. I can then run the web form and it updates the record normally.
Here is my situation: I use the SqlCommond to update some records in the SQL Server in a ASP.NET web site. Users can choose which records they want to update. Sometimes they may choose 40 or 60 records to update at a time.Is there any good way to do it? I do not want to do like it
im doing an update in web page by pressing a button. At the start some of the update was from a trigger, but i decided to put the hole update statement in the web page as an updatecommand in VB. this is 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.The statement has been terminated.and this is the code.
[Code]....
the error is in the updatecommand. And i dont know what is it.
I get the fol error: An exception of type 'System.Data.SqlClient.SqlException' Occurred in System.Data.dll but was not handled in user code..Subquery returned more than 1 value. This is not permitted When the subquery Follows =,! =, <, <=,>,> = Or When the subquery is used as an expression.
IF EXISTS (SELECT CustomerId FROM Customers where CustomerId= (SELECT Id FROM @tblCustomers )) UPDATE Customers SET Name=(SELECT Name FROM @tblCustomers ), Country=(SELECT Country FROM @tblCustomers ) WHERE CustomerId=(SELECT Id FROM @tblCustomers ); Else
Is it ok to do multiple records update with the following requirements and be safe that the operation will not get timed out? A user requiring me to do the following using a web form:
I. Display a list of accounts in a GridView for multiple records insertion with no client side validation. Each account will accept new reading and posted date values.
II. Once the user presses the submit button, the following steps will be performed for each record at server side:
1. Check if new input data is for new or old period. A checkbox is to be selected in the form. 2. Check if new data has existing related records. Else this is first time insertion; get consumption result and save to db. 3. If records found, get previous reading and date. 4. Check if new reading and date is greater than previous reading/date else should inform the user. 5. Get new consumption = new reading - previous reading and some other operation. 6. A join select statement will be used to get the lowest and highest values (consumption) for current account. 7. Compare the new consumption result with previous consumption to give a status indication for new reading as either high, low or normal to be saved into the db. (# 6 values will be used here) 8. If everything goes ok, save new data into 3 different tables. 9. Display the GridView with new data.
The SQL server and the hosted page are in two different servers.
select NotificationID, (select u.UserName from aspnet_Users u, notifications n where u.UserId = n.FromID) as 'UserName', [Message], DateCreated, TypeID from Notifications n, aspnet_Users u
I always received an error of: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
* 100 AS PERCENTAGE FROM support.SRFILE INNER JOIN BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = SRFILE.SRONUMBER WHERE (BSFLBWF_1.Closex IS NULL) AND (BSFLBWF_1.DedTypex = 'School' OR BSFLBWF_1.DedTypex = 'LAN-MLE') UNION ALL
The rest of the query continues as the above albeit selecting the same data from different tables and works fine.
The issue I have is that I want to Select some more data (another SELECT query) but with a different Where clause e.g. WHERE (BSFLBWF_1.KPIx NOT LIKE 'KPI%' OR BSFLBWF_1.KPIx NOT LIKE 'Proactive%') AND (BSFLBWF_1.Availabilityx LIKE '')
The problem is that this WHERE clause returns data which the above in bold has null values for (and thus doesnt work). Im still selecting the same data its just the WHERE Clause has changed. Is it possible to run such a query? If so, can it be done via a sub query?
I need to write a query (SQL Server) which gives me data according to the tags say if I want bookIds where tagid =9 it should return bookid 113421 and 113422 as it exists in both the books, but If I ask data for tags 9 and 10 it should return only book 113421 as that is the only book where both the tags are present.
B reports to A C reports to A D reports to A E reports to B F reports to B
and so on......
So, when A logins he should be able to see B,C,D(who are reporting to A) and also E,F(who are actually reporting to B and not directly to A)Please help to write such sql query.
I have a web portal designed in ASP.NET in which we ask customers to enter the data. On the click of submit button all i have done is, just read the data and called a stored procedure which inserts that into a table. There seems to be a problem in SQL Server 2005 while inserting the data from multiple computers at the same time. We have tested in our lab with three computers, result is that we get the data inserted successfully in only one machine and on the other two machines we get error on page. I have used transactions in the stored procedure and also tried setting the isolation levels to READ_UNCOMMITTED, SERIALIZABLE and SNAPSHOT. Nothing seems to work properly.
I have a SELECT query which grabs data from two different tables however when I run the query the CurrentCallOwner often pulls up the same name twice albeit with a different SLAFailed value. What I am trying to do is bind those two values into one. I thought that running a subquery and using a count would solve this however I all receive are a bunch ones. Do you know how I could bind those values into a single value (i.e. add them up?)
SELECT CurrentCallOwnerx, SLAFailed, COUNT(CurrentCallOwnerx) AS Expr1 FROM (SELECT BSFLBWF_1.CurrentCallOwnerx, COUNT(BSFLBWF_1.Incidentx) AS SLAFailed FROM .SRFILE INNER JOIN BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = SRFILE.SRONUMBER WHERE (SRFILE.SR_GROUP = '38') AND (BSFLBWF_1.KPIFailx = '1') OR (BSFLBWF_1.SchoolChargex > '0.00') OR (BSFLBWF_1.LANMLEChargex > '0.00') GROUP BY BSFLBWF_1.CurrentCallOwnerx UNION SELECT BSFWES_1.CurrentCallOwnerx, COUNT(BSFWES_1.Incidentx) AS SLAFailed FROM SRFILE AS SRFILE_1 INNER JOIN BSFWES AS BSFWES_1 ON BSFWES_1.Incidentx = SRFILE_1.SRONUMBER WHERE (SRFILE_1.SR_GROUP = '38') AND (BSFWES_1.KPIFailx = '1') OR (BSFWES_1.SchoolChargex > '0.00') OR (BSFWES_1.LANMLEChargex > '0.00') GROUP BY BSFWES_1.CurrentCallOwnerx) AS derivedtbl_1 GROUP BY CurrentCallOwnerx, SLAFailed
CDC or Change data capture is a new feature in SQL Server 2008, which is an ability to record changes to table data into another table without writing triggers or some other mechanism, Change data capture records the changes like insert, update, and delete to a table in SQL server.I have sql 2005 and I have created triggers and tables CDC to capture the data. everything functions good and the data is being updated in the _CDC tables. all i need to do now is to generate the updated data as a report (excel/html)- what should I do ......?i need to show only the updated columns when user selects the date periods ( Range between dates ) and the person name - i need to display any updated info about that person during that period. So this updated columns about this person should be displayed in excel formatted column wise.
Using a SubSonic (2.2) SqlQuery object, I am querying a view that contains distinct rows from another table. The results of the query, however, contain multiple rows for certain rows in the view. It appears to be because of a join on a temporary table in the query generated to achieve paging. How can I avoid this duplication of rows?
Bonus points: I have to use the view because SubSonic can't do .Paged() and .Distinct() at the same time. Why not?
select UM.USER_NAME, RM.ROLE_ID, RM.ROLE_NAME FROM [MICommonDB].[dbo].[ROLE_USER_MAPPING] as RUM join [MICommonDB].[dbo].[ROLE_MASTER] as RM ON RM.ROLE_ID=RUM.ROLE_ID join [MICommonDB].[dbo].[USER_MASTER] UM ON UM.USER_ID = RUM.USER_ID where UM.USER_NAME='useruser' which is giving me proper result(i.e., 2 records) when running in SQL server.
Now I have created below method in a class with same query (I am calling this method inside Controller):
public IEnumerable<RoleUserDO> getRoleNameByUserName(string userName) { IEnumerable<RoleUserDO> strResult = null;
strResult = (from RUM in oDBContext.ROLE_USER_MAPPING
[CODE]..
but it returns only 1 record instead of 2 records(when compared with DB query)
The goal is to update multiple row in the table. In my table there is 3 col, at here i will name it col1, col2 and col 3. The number of row in the table is, let say 10. Each row will have a different value in col1 and col2, where col3 value is static.
Something like the below; |col1 |col2 |col3 Row1 |a |b |c Row2 |aa |bb |c Row3 |aaa |bb |c
My update statement;
UPDATE table1 SET Col2 = CASE Col1 WHEN 'a' THEN '1' WHEN'aa' THEN '2' End where col3 IN (c)
In plain, i just wanna update col2, but had to base on col1 and col3 value. After I run the statement above, it will empty Row3's col2, but in fact, i don't want to update Row3's col2.
I'm working on an ecommerce project that is missing 5,000 product pictures.
I am trying to assign all products without pictures into a category located on a mapping table.
I know an insert can be accomplished from one table (or subquery) to another but I only need to find the product IDs and insert them with default values into the category mapping table that has all non-null columns.
The following code is the farthest I can get without help (I'm not strong in SQL), it won't work because my subquery returns many rows while the SQL statement is designed to insert one - I don't know how to handle this...
[Code]....
Any help would be appreciated from those more experienced than I. BTW... there are no known search results for this type of solution - if there is even a solution.
