Wednesday, 28 June 2017

SQL trouble

Some SQL trouble.

This SQL doesn't do what you might think. It will update all entries in Exam_Result not just andy's exams
update Exam_Result set score = 99 FROM Student s, Exam_Result er WHERE = er.student_id and'andy'; 
This is what you wanted to do:
update Exam_Result set score = 99 FROM Student s WHERE = Exam_Result.student_id and'andy';

And this is how to fix it from a DB backup via CSV after it has gone wrong:

\copy Exam_Result (exam_id, student_id, score) to out.csv CSV;
create table fix(exam_id int, student_id int, score int);
\copy fix from out.csv CSV;
update Exam_Result set score = f.score FROM fix f where f.exam_id=Exam_Result.exam_id and f.student_id=Exam_Result.student_id;

No comments:

Post a Comment