0

I have a table called students with 1000 students in. I have a query which tells me which of those students has free tuition. In the stduents table I have a field called FreeTuition and I want to populate/update that field with the results of the query. Do I need to use some kind of loop?

The students table has StuCode which is unique, the query returns StuCode of all the students with free tuition. This is how I want it to look:

| StuCode | FreeTuition |
-------------------------
| S12345  | Yes         |
| S12346  | No          |
-------------------------
tonyyeb
  • 719
  • 5
  • 13
  • 32
  • 1
    Can you post the table structure? I assume there is a unique ID column that you can match on? – Jacob H May 12 '17 at 12:52
  • 1
    Kindly provide data and table structure – Jim Macaulay May 12 '17 at 12:53
  • Hi is this free tuition query from same table? If you can share the query I can give you sample – devil_coder May 12 '17 at 12:53
  • Please no loop. Inner join table and query on StuCode, and make sure query's StuCode is unique as well. – Wendy May 12 '17 at 12:59
  • Your query seems to be similar to this [Update Statement](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) – DataWrangler May 12 '17 at 13:03
  • Not enough information for a response – Eli May 12 '17 at 13:05
  • @Eli what more information do you need? Query returns a studentid (StuCode) and I want to populate a column in a table full of unique StuCodes whether they occur in the returned query or not. – tonyyeb May 12 '17 at 13:08
  • a basic outline of what your table with the free tuition looks like (how you figure that out), and also what column you're trying to update. – Eli May 12 '17 at 13:18

1 Answers1

2

Not at all. Something like this:

with yourquery as (
      <your query here>
     )
update s
    set FreeTuition = (case when yq. StuCode is not null then 'Y' else 'N' end)
    from students s left join
         yourquery yq
         on s. StuCode = yq. StuCode;

Note: This sets the value for all students, yes or no. You can change the left join to just join to set the value only for students returned by the subquery.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786