A way to append & update a table query

[aspiringDBA]

Hiya all,

I have a db that holds classes that are being taken by students. In the main students tbl we have a yes/no field for CURRENT students.

I want to make a query that will allow me to send the data of the current students to a backup tbl (append query) AND set all the current students to null (Update query).

The update part is most important because we want to be able to set all current fields to null when we start another class (we won't have to manually uncheck each student from a finishing class in order to mark others for the next class).

We want to keep a table of previous class students (the backup tbl) just in case someone run the update query before we need them to.

Thanks for any suggestions!

[skhanal]

create a backup table like your student table and do

insert into backuptable
select * from student

update student set status=null

[aspiringDBA]

That's two separate queries?

[jkoopmann]

when reading this, it seems that you have not given quite complete information as there are a few unanswered questions. in particular i think your backup table of students might need a date field to it and what exactly are you baking up, the students, calsses, or a combination.
you really have a few use cases that you should map out here along with giving us the table definitions.