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.

UPDATE dataTable
SET dataTable.atAboveBelow =
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

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
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
      , 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!
# Posted By Ken Auenson | 3/23/09 6:02 PM
you should always avoid subqueries as they are very taxing in large result set, you can use joins when doing updates to avoid the subqueries:

update dataTable
set atAboveBelow =
   when d.readingScore < s.belowAvg then -1
   when d.readingScore > s.aboveAvg then 1
   else 0
   dataTable d
   inner join scoreMatrix s
      on d.gradeLevel = s.gradeLevel
      and testWindow = dataTable.testWindow
# Posted By tony petruzzi | 3/24/09 4:46 PM
Thank you both for your input. I didn't know you could use a join in that instance for an update.
# Posted By Joe Gautreau | 3/24/09 4:59 PM
Hi, your blog is very helpful. Thank you for the information and settings. I am interested in how to update?
# Posted By Nina | 7/20/09 5:42 PM