.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Post New Web Links

Why does'nt an UPDATE query finish

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
I have been asked the following question in one of SQL interviews: "An application issues an UPDATE statement to a table. But the UPDATE query never ends i.e it is going on for ever. What might be the reason?" I gave various reasons: --> May be the table is blocked by some other query. --> May be the query is updating a huge chunk of data. --> May be appropriate indexes are not created on the table. --> May be the indexes are defragmented. --> May be the statistics on the table need to be updated. --> May be the server is too busy to process the request. But the interviewer said all the above conditions are satisfied i.e. the query is updating only 100K rows and the table has appropriate rebuilt indexes with statistics updated and the server is not very busy. There is no blocking as well. What else could be the reason?   Thanks, Chaitanya

View Complete Post

More Related Resource Links

Running a single SQL update query on multiple foxpro databases

Hi, Env : SSIS 2008 My requirement is to run a update SQL statement on multiple foxpro databases. The fox database paths are available in a SQL server table.  I've created a for each loop control and assigned the database path to a variable named dbpath. Within the loop, I added an "execute sql task" control. Now what properties are to be set to get the requirement done. Appreciate any help. Regards. BH  Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Query DB and update Check Box List on JS/jQuery Function

Hey guys.  I'm not sure if this is the right forum to post this in but I've come to a roadblock in my app and I'm not sure which way I should go.  Currently, I have a slider control and two text boxes used to set the Start and End times of a section of a video.  Let's say the video is of some Music Awards.  In my database, I've tagged the start and end time of each celebrities on-screen appearance.  When the user adjusts their start and end times on the page, I want to query the database table and pull back all celebrities within that range.  The problem is I don't know how to do this in JS.Can I use the Check Box List and bind the values to it?  Do I need to render a table of Check Boxes in JavaScript?  Do I call the Server-Side code via Ajax and then partially render the page?Any help, comments or suggestions would be great.

Select and update in same query

Hi all, I need to update the selected rows from a select statement and update it using an update query. For that the query i am using is UPDATE SERVERACCOUNTSTRAN..TRAN_JOURNALDET SET CONTRATRANSACTIONHEADCODE=T.CODE FROM ##TRAN TR INNER JOIN SERVERSSRETAIL..TRANSACTIONHEAD T ON T.SHORTNAME=TR.CREDT_ACCT WHERE T.COMPANYCODE=1 AND CONTRATRANSACTIONHEADCODE=0 Now the problem is all the values of contratransactionheadcode field is updated to 18. But the real values are different for each row. only the first row value is 18 which is updated to every other row. Thanks in advance.Sujith Kumar www.sujithkumar.tk

Can anybody Identify whats the error in this update query ??

I am getting error as Invalid Column name 'JAVA' when i am havin a value JAVA in txtExamName text box.In my database ExamName field is having a data type nvarchar(25)."UPDATE Exam SET ExamName=' " + txtExamName.Text + " ',SubjectId=" + SubId + ",StartDate=' " + txtDate.Text + " ',StartTime=' " + txtHour.Text + ":" + txtMinutes.Text + " ',EndTime=' " + txtHour0.Text + ":" + txtMinutes0.Text + " ',ExamDescription=' " + txtDescri.Text + " ',TotalNoOfQuestion=" + txtQue.Text + ",MarkofCorrectAns=" + txtPoint.Text + ",MarkofWrongAns=" + txtExamName.Text + " WHERE ExamId=" + Convert.ToInt32(Session["ExamId"])

How index will effect in update query

Hi All, I have one update query it has lot of joins with other table ,If we add index in the secondary table with this join condition will it improve the performance? eg: UPDATE FunctionalAcknHeaders  SET cStatus = 'E'  FROM FunctionalAcknHeaders fh   LEFT JOIN OrderHeaders oh ON cast(oh.nOrderHeaders_id as varchar(10)) = 'abc'  WHERE oh.nOrderHeaders_id is null   AND fh.cStatus = 'R'   if i create index on OrderHeaders  table nOrderHeaders_id, will it improve the performance ? Thanks, Roshan

help with query - update from 2 tables

hi i have 2 tables     table1: num,X,Y     table2: num,X,Y i need to update X and Y in table1 where table1.num = table2.num how to do it ? i need it in Oracle query (i think that in sql server it will work too) thank's in advance 

Update query help Dense_Rank .....

Hi,  I need help updating MinAmounttBilled and Dense_Rank_ fields in the table @MinNums_TestData below. MinAmountBilled - For each customer this field should be populated with the smallest value in the AmountBilled column. Dense_Rank_ - This field groups all same customer numbers, need to know how i can update this filed as well. Actuall table i will be updating has 10 million records. Here si the Sample data :- -- Create Sample Table Declare @MinNums_TestData TABLE ( CustomerNumber [varchar](20) , Dense_Rank_ [varchar](20) , AmountBilled [varchar](20) , MinAmounttBilled [varchar](20) ) -- Test data INSERT INTO @MinNums_TestData ( CustomerNumber, Dense_Rank_,AmountBilled,MinAmounttBilled) SELECT '100', '', '15243' ,''UNION ALL SELECT '100', '', '1243' ,''UNION ALL SELECT '100', '', '34243' ,''UNION ALL SELECT '100', '', '43' ,''UNION ALL SELECT '200', '', '56' ,''UNION ALL SELECT '200', '', '65' ,''UNION ALL SELECT '200', '', '34243' ,''UNION ALL SELECT '9', '', '6' ,''UNION ALL SELECT '9', '', '5' ,''UNION ALL SELECT '20', '', '34243' ,''UNION ALL SELECT '200', '', '56' ,''UNION ALL SELECT '3', '', '2' ,''UNION ALL SELECT '1', '', '3' ,'' -- Table to group all customers Declare @MinNums_TestData_2 TABLE ( CustomerNum

How do I create a query that joins two tables and returns a single entry with the most recent update


Say I have the following two tables (Purchase & Customer). 

CustomerID StoreName PurchaseDate
1 Asheville 9/14/2010 15:49

update query having minimum of expresion



I have this 3 tables:

CREATE TABLE [dbo].[TC_103_M](

	[TIP_REC] [int] NULL,

	[ID_CMP] [int] NOT NULL,

	[ID_FILE] [int] NULL,

	[CUT_OFF] [datetime] NOT NULL,

	[FIN_DTST] [datetime] NULL,

	[FIN_DTVA] [datetime] NULL,

	[FIN_DTSC] [datetime] NULL,

	[FIN_DIV] [varchar](3) NULL,

	[FIN_TIPTAS] [varchar](2) NULL,

	[FIN_PREAMM] [varchar](1) NULL,

	[FIN_RIMB] [varchar](2) NULL,

	[FIN_DTIA] [datetime] NULL,

	[FIN_DTCA] [datetime] NULL,

	[FIN_FRCAP] [varchar](8) NULL,

	[FIN_AMERO] [float] NULL,

	[FIN_AMNOM] [float] NULL,

	[FIN_DEBRES] [float] NULL,

	[FIN_AMINS] [float] NULL,

	[FIN_EAD] [float] 

Update query from select statement


Cana you I need to do an update from a select statement...i'm really close here but have not quite figured out the nuance...

the full select statement provides correct results and i've joined the outer table with the inside table....can you make recommendations?

BEGIN TRAN --rollback

UPDATE geprs_price..T_ITEM_PRC_CHG 


		FROM geprs_price..T_ITEM_PRC_CHG gep
		and gep.prc_eff_dt = dm.prc_eff_dt
		LEFT JOIN construct..T_AD_USERS con
		ON dm.CONFIRM_BY  = con.UID
		LEFT JOIN construct..T_AD_USERS crt
		ON dm.CREATE_BY  = crt.UID

		AND dm.PRC_TYP_ID in (39)



Adding a new column in update query


Hi all,


Is it possible to add a new column in an update query? I am joing two tables  with [Encountered Month] from Table 1 and [Calendar Date] from table 2 and trying to add one column[Fiscal Month] into the first table where  dbo.table1.[Encountered Month]=dbo.table2.[Calendar Date]

Appreciate your help.

update query qith inner join


i have 3 tables ,and i have inserted data in table A from  tables B and C into it

"my A table has column"
Coe_Code , Coe_name , Sdo_code, Sdo_name

"B table has column"

COE_ID,  Coe_Code , Coe_name  (COE_ID is auto incremented)

C table has column

Sdo_id ,Sdo_code , Sdo_name, COE_ID  (Sdo_id is auto incremented and COE_ID coming from B table )

i have inserted data into B table from A
after that i have inserted data into C by taking column from A and B as you can see column and table above by using this query

[insert into C (Sdo_code,Sdo_name,COE_ID ) 
select A.Sdo_code,A.Sdo_name,B.COE_ID from A,B where A.Coe_Code =B.Coe_Code

but my problem arise when i need to update my C table ,i need to update only 2 column (Sdo_name,COE_ID ) in C table coming from 2 different table
i tried so many query using inner join with update  but was not able to update that..

if some one could help me to provide the query..i also tried this query but didnt work...
[update C set Sdo_code=A.Sdo_code,COE_ID=B.COE_ID from C innerjoin A on A.Sdo_code=C.Sdo_code innerjoin B on C.COE_ID=B.COE_ID]

please provide me the query or help me in what ever way you can

Update Query in aspnet_Profile table


I am using .net membership properties that uses aspnet_Profile tbl to store property name in column PropertyNames and their values in PropertyValuesString with semicolon delimiter so that

UserId PropertyNames PropertyValuesString
9F2B0B68-2E0B-4640-B9A9-8BDBC52385C7 imageORavatar:S:0:1:Title:S:1:2:FirstName:S:3:5:Avatar:S:8:10:AboutMe:S:18:4:LastName:S:22:5:Company:S:27:4:AcceptMailUs:S:31:5:Newsletter:S:36:4:AcceptMailOthers:S:40:5:Address:S:45:3: 2mruser5Winter.jpgewwruser5testFalseTrueFalsenil

No i want to write an Update query that update the propertyAvatar against Userid but dont know how to write it, Any help would be really appreciated.

Conditional update query?



I will need to update a value into one column provided the state(different column) is in OPEN, STUDY or NEW.

Here is the query I am having issues with. Can you guys help.






[Customer-Encountered-Burts].Category = 'Non-Terminal'

SELECT and UPDATE query problems



I have a web form that should display advertisements depending of the number of impressions (number stored in a database) and I also need to update the number of impressions when page is loaded. The advertisements are displayed on a web page, but the problem is I don't know where to insert the SqlCommand for updating number of impressions.

Labels are used for displaying the number of impressions.  

        String connectionString = WebConfigurationManager.ConnectionStrings["CMS"].ToString();
        using (SqlConnection connection = new SqlConnection(connectionString))

            using (SqlCommand commandReklama = new SqlCommand("SELECT * FROM Reklama", connection))
                SqlDataReader readerReklama = commandReklama.ExecuteReader();
                while (readerReklama.Read())
                    string redirectUrl = readerReklama.GetString(1);
                    string url = readerReklama.GetString(2);
                    int brojPrikazivanja = readerReklama.GetInt32(3);
                    bool show = readerReklama.GetBoolean(4);                  
                    string naziv = readerReklama.GetString(5);
                    int location = readerReklama.GetInt32(6);

                    if (

UPDATE query based on the SELECT query



Can you please advise how can i achive following UPDATE query based on the SELECT query. You can see my comments in Green

UPDATE [Table_POforecast]
   SET [ForecastAmount] = [ForecastAmount]* Coeff --- Coeff value to be from SELECT Statement
 WHERE [PO_No] = [PO_No] --- PO_No to match from SELECT statement

--- SELECT Statement
SELECT     PO_No, (NewBalance / TotalForecastAmount) AS Coeff
FROM       View_Forecast
WHERE     (OutstandingForecast < 0)



vastly different performance of update query on development machine and on server



Using VisualWebDeveloper 2010 on the development machine. All nice and fast.

Using VisualWebDeveloper 2008 on the server. Server runs on Windows Server 2008 and has IIS 7.

It looks like that when 'table', a datatabe filled up in C# code behind, is getting longer (say 100 rows), then the server (whether running the website on IIS or on the local host of VIsualWebDeveloper2008!!!) is really slowing down. Checked it in debug mode and traced it down to


This line takes 5 seconds to execute, with 100 rows. On the development machine, it takes almost no time.

How can that be?

Are there any know issues? Could it be that there is incompatibility between different version of SQL server or something like that? The update works, it is just awefully slow, but only on the server.

What's going on? And how to solve it?

Thank you.

Best regards,


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend