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)
Best regards
Melissa
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.