Hello experts,
(Sorry for the long post)
I have this query (below at the very end) to update a field-->
Step 3:'Closed_Status' in a table 'Students' (set it to 1 based on some conditions or default will be 0)
Step 1: So the query is taking other fields 'RollNumber','StdActivity',StartDate','EndDate' from the same table 'students' to a #temptable1
Step 2: Then taking only 'RollNumber' to another #temptable2 based on where condition as below;
SELECT RollNumber INTO #temptable2 FROM #temptable1
WHERE EXISTS
SELECT TOP 1 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
HAVING SUM(ST.StdActivity) >= 8)
I want to understand the inner part of this WHERE EXIST() which is --> SELECT TOP 1 1 FROM Students ST
I believe since you are using TOP 1 1...the result will be always '1' , doesn't matter what conditions you use for filtering the records right?
so in the below query, what's the use where clause part in the query(Does this even taken into consideration to return 1 or 0)???
Isn't it similar to this below-->
SELECT RollNumber INTO #temptable2 FROM #temptable1
WHERE EXISTS
SELECT TOP 1 1 FROM Students ST
Full Original Query
---------------Step 1:--------------------------------
SELECT
RollNumber
, StdActivity
, startdate
, Enddate
INTO #temptable1
FROM Students
WHERE D_No > ''
---------------Step 2:--------------------------------
SELECT RollNumber INTO #temptable2 FROM #temptable1
WHERE EXISTS
SELECT TOP 1 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
HAVING SUM(ST.StdActivity) >= 8)
---------------Step 3:--------------------------------
Update Students
Set [Closed_Status]=1
From Students
INNER JOIN #temptable2 ON ST.RollNumber= #temptable2.RollNumber
ERROR: All the students are having 'Closed_Status' as '1' which is not right
Thanks in advance
Jenni
While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
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
TOP 1 1 means nothing in this context; it only serves to make things cryptic. In an EXISTS subquery, it does not really matter what you put after SELECT, and people normally put an *. (But given the GROUP and HAVING SQL Server may object, so you have to put a 1 there - but without the TOP 1.
As for why you get the wrong result, we can't tell. We need to see your table definitions and sample data, preferably as CREATE TABLE + INSERT statements.
As YithakKhabinsky-0887 noted, supplying sample tables and data and the desired result from that sample data will get you faster, better answers.
That said, WHERE EXISTS(...) returns true if the query inside the parenthesis returns at least one row. It doesn't make any difference whether it returns 1 row or 1,000,000 rows. Because of that SELECT 1 1 ... and SELECT 1 ... and SELECT * ... all do exactly the same thing and are equally efficient.
Without the sample data and desired result, it's hard to know for sure what change(s) you need. But my guess is your problem is that you are not tying #temptable1.RollNumber to ST.RollNumber inside your WHERE EXISTS(...). So try
WHERE EXISTS
SELECT TOP 1 1 FROM Students ST
left outer join ProjectDetails PD
on ST.RollNumber = PD.RollNumber
and PD.Code like 'PD128..'
and #temptable1.RollNumber = ST.RollNumber /* this is the line I added */
WHERE ST.StartDate>=#temptable1.StartDate
AND ST.EndDate<=#temptable1.EndDate
AND PD.CompletionStatus IS NULL
HAVING SUM(ST.StdActivity) >= 8)
Hello everyone,
Oopsie my bad..I forgot to upload the pic I took of my table's data and 'create' script.
But @MelissaMa-MSFT got that right and explained the difference between them quite well :)
Thanks for that and as you suggested, changing the update script fixed the issue.
Again, appreciate your help from all of guys :)
Thanks
