Mar232009
SQL - Update Query Using Sub Queries Update Value
Goog Feed, General, SQL
Comments (4)
I like to post anything SQL that is "new" to me that perhaps I didn't know before or don't remember doing. Today was one such occasion.
I was challenged with the task of updating a table full of reading data for for our district's 3rd-5th graders. The data was comprised of a record per student per testing cycle, and was used for the purpose of tracking reading fluency. I have a second table which holds a score matrix letting me know per grade, per testing cycle what the low and high grades should be to determine if a student is above average, below average or at average.
This was something that I had to do as a "one time event" to about 7000 plus records. I decided that I could run a bunch of queries per reading cycle per grade to do this, but that seemed cheesy to me. I wanted to do it all in one fell swoop. Here is what worked for me.
SET dataTable.atAboveBelow =
CASE
WHEN dataTable.readingScore < (SELECT belowAvg FROM scoreMatrix where gradeLevel = dataTable.gradeLevel AND testWindow = dataTable.testWindow) THEN -1
WHEN dataTable.readingScore > (SELECT aboveAvg FROM scoreMatrix where gradeLevel = dataTable.gradeLevel AND testWindow = dataTable.testWindow) THEN 1
ELSE 0
END
I realize that you are dealing with an existing system and making table and/or schema changes is probably not something you are able to do in this instance, but I wanted to add an alternate approach.
If I have an instance where a column is based off of information in a seperate table, then I try to create a view for my main table that calculates the value from both tables.
So below, I have a view dataTable_v that could be used in place of dataTable for all instances where datatable is being queried (just replace col# with the actual column names).
Then in the future, if anything changes in scoreMatrix, the dataTable_v will have the most up-to-date data.
create view dataTable_v
as
select
t1.col1
, t1.col2
, t1.col3
, CASE
WHEN t1.readingScore < t2.belowAvg THEN -1
WHEN t1.readingScore > t2.aboveAvg THEN 1
ELSE 0
END as atAboveBelow
from dataTable t1
left outer join scoreMatrix t2 on t1.gradeLevel = t2.gradeLevel and t1.testWindow = t2.testWindow
Hope this helps someone!
--
Ken
update dataTable
set atAboveBelow =
case
when d.readingScore < s.belowAvg then -1
when d.readingScore > s.aboveAvg then 1
else 0
end
from
dataTable d
inner join scoreMatrix s
on d.gradeLevel = s.gradeLevel
and testWindow = dataTable.testWindow