SQL Update – An Introduction

A characteristic of persistent storage — including the relational databases upon which SQL operates — is the ability to create, read, update, and delete information; also known as “CRUD.” The CRUD actions map to these SQL operators:

This article explores SQL Update, the ability to change previously-stored database values.

Syntax of SQL Update

Please pay particular attention to the WHERE clause of the SQL Update statement; it specifies which rows should be affected by the SQL Update. If you omit the clause then all the records in the table will be updated (which is rarely the intended behavior, although I demonstrate a good use for it below).

UPDATE
    table_name
SET
    column_1 = value_1,
    column_2 = value_2, ...
WHERE
    condition ;

This article explores the most common SQL Update use cases and the syntax needed to make things happen. 

A Sample Table for SQL Update

Our first table describes the pupils enjoying the benefits of higher learning. This very minimal table consists of their name and a numeric course ID. (A real-world implementation would have student IDs, email and postal addresses, tuition balance, etc.)

STUDENTS:

SQL Update: Single Row Value

This example of a SQL Update will change a single value (COURSES) in the STUDENTS table row with ID=2.

(Because SQL Update doesn’t echo output when using an online SQL experimentation tool we’ve added a SQL Select command to dump the table after the SQL Update. Run these SQL code samples yourself at SQL Fiddle. The SQL to create and populate this sample table is included at the end of this article. For the following examples add the Select to the code snippet.)

UPDATE
    STUDENTS
SET
    COURSES = 7
WHERE
    ID = 2 ;
SELECT * FROM STUDENTS ;

After the SQL Update the COURSES value in ROW ID=2 has been changed.

SQL Update: Single Row Multiple Values

UPDATE
    STUDENTS
SET
    COURSES = 365,
    NOTES = 'loves kittens'
WHERE
    ID = 2 ;

After the SQL Update two values in ROW ID=2 have been changed.

SQL Update: Multiple Rows

UPDATE
    STUDENTS
SET
    FIRST = 'Suzanne'
WHERE
    FIRST = 'Susan' ;

After the SQL Update each student which had a first name of “Susan” (in the FIRST column) is now named “Suzanne.” Modifying multiple rows in one SQL Update is known as a “bulk update.”

SQL Update Data Operations

Arithmetic Operations

SQL supports arithmetic operators, so you can increment the number of courses a student is enrolled in this way:

UPDATE
    STUDENTS
SET
    COURSES = COURSES + 1
WHERE
    ID = 1 ;

After the SQL Update, for the row with ID 1, the COURSES column values have been incremented by one.

String Operations

SQL also supports string operators to manipulate text values. This example shows how to prepend a text string using the “||” shorthand for SQL Concat. SQL string functions are extensive and will require another blog post to explore. 

UPDATE
    STUDENTS
SET
    NOTES = 'really ' || NOTES
WHERE
    ID = 2 ;

After the SQL Update the NOTES column has been changed in ROW identified with ID=2.

SQL Update All Columns

Remember that tip about ensuring that you’ve always got a WHERE clause to prevent unintended updates? Sometimes that’s exactly what needs doing; for example at the end of a semester all the students are done with their courses and so we reset the count to zero for everyone via a SQL Update without a WHERE clause.

UPDATE
    STUDENTS
SET
    COURSES = 0 ;

After the operation the STUDENTS table shows the results.

Conclusion

SQL Update is one of the core interactions — create, read, update, and delete (CRUD) — you’ll have with stored data. Combined with arithmetic and string operations, honed by search criteria of the WHERE clause, SQL Update is the bridge between computing routines and the data you persistently store.

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.

Start Learning

SQL Update Sample Table Code

The following SQL will build and populate the sample table used in this article.

CREATE TABLE STUDENTS (
    ID INT NOT NULL,
    FIRST VARCHAR(33),
    LAST VARCHAR(33),
    COURSES INT,
    NOTES VARCHAR(80) ) ;

INSERT INTO
    STUDENTS
VALUES
    ( 1, 'Alexandria', 'Ocassio-Cortez', 0, '' ),
    ( 2, 'Ilhan', 'Omar', 0, '' ),
    ( 3, 'Nancy', 'Pelosi', 0, '' ),
    ( 4, 'Susan', 'Brooks', 0, '' ),
    ( 5, 'Susan', 'Wild', 0, '' ) ;