Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

PostgreSQL INSERT, UPDATE, and DELETE Commands

Introduction
Inserting and updating NULLs
Conditions in UPDATE and DELETE
Updating the list of columns in one query
Using values from another column
Return rows in INSERT, UPDATE, DELETE
18. Display the expression value in UPDATE
Summary

Instruction

Fantastic! Now we will analyze a query which changes a record, updating the column value by the expression value and displaying the expression value.

Have a look:

UPDATE exam
SET
  written_exam_score = written_exam_score + 2
WHERE student_id = 9
RETURNING
  written_exam_score,
  oral_exam_score, 
  (written_exam_score + oral_exam_score) AS sum_score;

This returns the result:

written_exam_score oral_exam_score sum_score
... ... ...
17 11 28

This query updates the data of the student with id = 9 in the column written_exam_score. Specifically, it adds 2 to the existing value:

written_exam_score = written_exam_score + 2

Now the student has a higher score on their written exam. (It was 15; now it's 17.) After the record is updated, the query displays three values: the new value for the column written_exam_score, the value of the column oral_exam_score, and the sum of both exam scores:

(written_exam_score + oral_exam_score) AS sum_score

We named this displayed expression value sum_score.

Exercise

The teacher forgot to enter a student's score for the oral English exam. The student ID is 10, and the exam score is 11.

Update this record and use RETURNING to display the following data: the student ID, the subject name, the oral exam score, and the sum of the oral and written exam scores for the subject English. Name the new returned expression sum_score.