what is this???

Question

what is this???

mixalissen

SSC-Addicted

Points: 443
More actions
September 30, 2008 at 11:52 am

#213506

UPDATE t
SET count_Successful_modulesOfProgram = CountModulesOfProgram
FROM @Tmp t,
(
SELECTcert_cand_programs.candidate_fk,
COUNT(cert_cand_programs_fk) AS CountModulesOfProgram
FROM cert_cand_programs
INNER JOIN cert_cand_programs_details ON cert_cand_programs.pk = cert_cand_programs_details.cert_cand_programs_fk
INNER JOIN Exam_Candidate ON cert_cand_programs_details.msv_fk = Exam_Candidate.module_syllabus_version_fk
AND cert_cand_programs.candidate_fk = Exam_Candidate.candidate_fk
INNER JOIN TestSet ON Exam_Candidate.testset_fk = TestSet.pk
WHEREExam_Candidate.isSuccessful = 1 AND
((Exam_Candidate.score IS NOT NULL AND Exam_Candidate.score >= TestSet.pass_mark)
OR
(Exam_Candidate.re_mark IS NOT NULL AND Exam_Candidate.re_mark >= TestSet.pass_mark))
AND pendency in (0,5) AND isAppproved = 1
GROUP BY cert_cand_programs.candidate_fk
)
does anyone know what is the purpose of this subquery???I have never seen this kind of updating statement...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

mixalissen (9/30/2008)
UPDATE t
SET count_Successful_modulesOfProgram = CountModulesOfProgram
FROM @Tmp t,
(
SELECTcert_cand_programs.candidate_fk,
COUNT(cert_cand_programs_fk) AS CountModulesOfProgram
FROM cert_cand_programs
INNER JOIN cert_cand_programs_details ON cert_cand_programs.pk = cert_cand_programs_details.cert_cand_programs_fk
INNER JOIN Exam_Candidate ON cert_cand_programs_details.msv_fk = Exam_Candidate.module_syllabus_version_fk
AND cert_cand_programs.candidate_fk = Exam_Candidate.candidate_fk
INNER JOIN TestSet ON Exam_Candidate.testset_fk = TestSet.pk
WHEREExam_Candidate.isSuccessful = 1 AND
((Exam_Candidate.score IS NOT NULL AND Exam_Candidate.score >= TestSet.pass_mark)
OR
(Exam_Candidate.re_mark IS NOT NULL AND Exam_Candidate.re_mark >= TestSet.pass_mark))
AND pendency in (0,5) AND isAppproved = 1
GROUP BY cert_cand_programs.candidate_fk
)
does anyone know what is the purpose of this subquery???I have never seen this kind of updating statement...

I'm not really sure, but the subquery appears to be a derived table. There appears to be a comma (,) between the table variable and the derived table, which also means that this query is also creating a cartesian product (cross join).

To answer your question in more detail, we need more details. The update query alone is out of context.

😎

mixalissen SSC-Addicted Points: 443 More actions · Answer 2

I am talking about the pattern::

UPDATE t

SET var1 = var2

FROM someTable,(

--aQuery

)

I dont expect that you will tell me what the code does...

Any ideas about the pattern of the statement???

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Same kind of thing as a select statement that has a derived table (subquery) in the from clause.

The subquery resolves to a rowset that's treated as if it were a table. It can be joined to others or referenced in the other clauses just like a table. It makes some queries easier to write.

It gives the same results as inserting the results of a query into a temp table and then using the temptable in the update

So, these two are equivalent in terms of results.

UPDATE t

SET var1 = var2

FROM someTable, (

--aQuery

)

-- equivalent to

Insert INTO #SomeTempTable

-- aQuery

UPDATE t

SET var1 = var2

FROM someTable, #SomeTempTable

If both of the entries in the from clause return more than 1 row, then you have a cross join that will generate more rows than you may well expect.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

You asked:

does anyone know what is the purpose of this subquery???I have never seen this kind of updating statement...

This is the only subquery I see:

(
SELECT cert_cand_programs.candidate_fk,
COUNT(cert_cand_programs_fk) AS CountModulesOfProgram
FROM cert_cand_programs
INNER JOIN cert_cand_programs_details ON cert_cand_programs.pk = cert_cand_programs_details.cert_cand_programs_fk
INNER JOIN Exam_Candidate ON cert_cand_programs_details.msv_fk = Exam_Candidate.module_syllabus_version_fk
AND cert_cand_programs.candidate_fk = Exam_Candidate.candidate_fk
INNER JOIN TestSet ON Exam_Candidate.testset_fk = TestSet.pk
WHERE Exam_Candidate.isSuccessful = 1 AND
((Exam_Candidate.score IS NOT NULL AND Exam_Candidate.score >= TestSet.pass_mark)
OR
(Exam_Candidate.re_mark IS NOT NULL AND Exam_Candidate.re_mark >= TestSet.pass_mark))
AND pendency in (0,5) AND isAppproved = 1
GROUP BY cert_cand_programs.candidate_fk
)

As for the rest, it just looks like an UPDATE query using the old style join that will result in a cross join without a where clause.

😎

Perry Whittle SSC Guru Points: 233821 More actions · Answer 5

Lynn Pettis (9/30/2008)
that will result in a cross join without a where clause.

and as explained will almost certainly produce more rows than you can shake a stick at 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

mixalissen (9/30/2008)
I am talking about the pattern::
UPDATE t
SET var1 = var2
FROM someTable,(
--aQuery
)
I dont expect that you will tell me what the code does...
Any ideas about the pattern of the statement???

Apparently the author of the code felt it necessary to use a GROUP BY to controll the filtering of the update. You can't use a GROUP BY in an update, so the derived table is necessary to produce a result set that the update CAN use.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)