It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Inserting and updating NULLs
Conditions in UPDATE and DELETE
9. UPDATE with conditions: AND, OR
Using values from another column
Inserting data from a query
Summary

Instruction

Good job! Up till now, we've written operations with only one condition in the WHERE clause. However, there are lots of situations where we'll need to use more than one condition. Here's an example:

UPDATE exam 
SET written_exam_score = 0
WHERE student_id = 1 
  AND subject = 'Spanish';

It updates the score of the written exam in Spanish for the student with student_id = 1. It checks if the student ID is 1 and then checks if the exam subject is Spanish. If both of these are true, then it assigns a zero to the written exam score. The AND logical operator joins these two conditions.

Of course, you can use more than two conditions. Remember that all conditions joined by an AND operator have to return TRUE for the rows to update.

Another operator for multiple conditions is OR. In this case, it is enough that one of the given conditions is met.

Exercise

Assign a score of 3 points to all students who do not have an oral English exam score date.

Stuck? Here's a hint!

Use:

WHERE oral_score_date IS NULL
  AND subject = 'English';