UPDATE SQL table based on query

Question

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 | -------------------------

Can you post the table structure? I assume there is a unique ID column that you can match on? — Jacob H
– Jacob H, Commented May 12, 2017 at 12:52
Hi is this free tuition query from same table? If you can share the query I can give you sample — devil_coder
– devil_coder, Commented May 12, 2017 at 12:53
Please no loop. Inner join table and query on StuCode, and make sure query's StuCode is unique as well. — Wendy
– Wendy, Commented May 12, 2017 at 12:59

Gordon Linoff · Accepted Answer · 2017-05-12 13:01:58Z

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.

Collectives™ on Stack Overflow

UPDATE SQL table based on query

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related