Hi @jennifer zen ,
For this type of problem we recommend that you post CREATE TABLE statements for your tables(students,ProjectDetails) together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.
SELECT TOP 1 Means Selecting the very 1st record in the result set.
SELECT 1 Means return 1 as the result set.
SELECT TOP 1 1 FROM [SomeTable] WHERE <SomeCondition> Means if the condition is true and any rows are returned from the select, only return top 1 row and only return integer 1 for the row (no data just the integer 1 is returned).
In addition, there is no difference in efficiency between (NOT) EXISTS (SELECT 1 ...) and (NOT) EXISTS (SELECT * ...).
You could try with below update at step2 and check whether it is working to you.
---------------Step 2:-------------------------------- SELECT RollNumber INTO #temptable2 FROM #temptable1 WHERE EXISTS ( SELECT TOP 1 1 --Consider whether to remove 'top 1' FROM Students ST left outer join ProjectDetails PD on ST.RollNumber = PD.RollNumber and PD.Code like 'PD128..' WHERE ST.StartDate>=#temptable1.StartDate AND ST.EndDate<=#temptable1.EndDate AND PD.CompletionStatus IS NULL AND #temptable1.RollNumber=ST.RollNumber --Add this condition here HAVING SUM(ST.StdActivity) >= 8)
Or you could update the whole statement like below:
Update a Set [Closed_Status]=1 From Students a where a.RollNumber in ( select st.RollNumber From Students ST left outer join ProjectDetails PD on ST.RollNumber = PD.RollNumber and PD.Code like 'PD128..' WHERE ST.D_No > '' AND PD.CompletionStatus IS NULL group by st.RollNumber HAVING SUM(ST.StdActivity) >= 8)
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table
It is better to use
SELECT TOP(N) ...clause with parenthesis.