VMware ESXi and vSphere Cluster Management

SQL UPDATE Statement

Learn how to use SQL UPDATE to change one or more existing rows, select rows safely with WHERE, update multiple columns, and verify results.

What the SQL UPDATE statement does

UPDATE is the SQL statement used to modify values that are already stored in existing table rows. A row is one record, and a column is a named field that stores a value for each row.

UPDATE is different from other common data statements:

• INSERT adds new rows.
• UPDATE changes values in existing rows.
• DELETE removes existing rows.

An UPDATE statement can change one row or many rows, depending on the condition in its WHERE clause.

Basic UPDATE syntax

The general structure is:

UPDATE table_name
SET column_name1 = value1,
    column_name2 = value2
WHERE column_name3 = value3;

• UPDATE table_name identifies the table whose data will change.
• SET specifies the column or columns to change and their new values.
• WHERE supplies a condition that selects the rows to change. A condition is a comparison or logical expression that determines whether a row matches.

Text values are normally written as string literals in single quotes. The semicolon marks the end of the SQL statement in systems and tools that use statement terminators.

Updating one column

Suppose the employee table contains an employee named John Doe whose email value is empty. In this example, an empty database value is represented by NULL.

To replace John Doe's missing email with a valid address, assign the new value to email:

UPDATE employee
SET email = 'john@email.com'
WHERE firstName = 'John'
  AND lastName = 'Doe';

The SET clause changes only the email column. The WHERE clause uses two conditions together to select John Doe.

Selecting the correct row with WHERE

WHERE limits an update to rows that match its condition. The logical operator AND requires every connected condition to be true. In the example, a row must have both firstName = 'John' and lastName = 'Doe'.

Whenever possible, make the condition identify exactly the intended row. Names can be shared by multiple people, so a name-based condition might update more than one employee. A primary key, such as employeeNumber, is usually safer. A primary key is a column, or group of columns, that uniquely identifies each row.

For example, if employee number 0 uniquely identifies John Doe, use it directly:

UPDATE employee
SET email = 'john@email.com'
WHERE employeeNumber = 0;

Updating multiple columns

A single UPDATE can change several columns. Put each assignment in the SET clause and separate assignments with commas:

UPDATE employee
SET extension = 'x2400',
    email = 'john.doe@example.com'
WHERE employeeNumber = 0;

This statement changes both extension and email for the employee whose primary key is 0. Do not place a comma between SET assignments and the WHERE clause.

Verifying the outcome

Before changing data, preview the rows selected by the condition with a SELECT statement:

SELECT employeeNumber, lastName, firstName, extension, email
FROM employee
WHERE firstName = 'John'
  AND lastName = 'Doe';

After the UPDATE runs, execute the same or a more specific SELECT query to check the result. The relevant row should now contain the new email address, while unrelated rows should retain their original values.

The number of affected rows reported by the database is the number of records changed by the UPDATE. Check that count against what you expected. If you intended to change one employee but the database reports many affected rows, stop and investigate.

UPDATE clause reference

Safe UPDATE practices

1. Preview the condition first. Replace UPDATE with SELECT while keeping the same FROM and WHERE logic. Confirm that the returned rows are exactly the rows you intend to change.
2. Prefer a primary key. Use a unique identifier such as employeeNumber instead of relying only on first and last names.
3. Review affected-row counts. Compare the reported count with your expectation before proceeding.
4. Use transactions for important changes. Where your database supports transactions, begin a transaction, run and verify the UPDATE, then commit it only after checking the result. Use rollback instead of commit if the result is incorrect. Exact transaction commands vary by database system.
5. Verify the changed row. Query the row after execution and confirm that columns not included in SET were not unintentionally changed.

Troubleshooting UPDATE statements

Every row receives the same value

The likely cause is a missing WHERE clause. Use a condition that identifies only the intended row or group of rows, and preview that condition with SELECT before running UPDATE.

More than one employee is updated

A name-based condition may match multiple employees because names are not guaranteed to be unique. Use employeeNumber or another unique key in the WHERE clause.

No rows are updated

The stored values may not match the condition because of spelling, whitespace, capitalization rules, or another comparison behavior. Run a SELECT query to inspect the actual values and correct the condition.

The wrong column changed

Review every assignment in the SET clause. Then query the row and verify the result. A typo in a column name or an incorrect assignment can change a different field than intended.

Exam-relevant notes

• UPDATE modifies existing rows; it does not create a new row.
• SET is required to state the new column values.
• WHERE controls which rows are changed.
• Without WHERE, all rows are candidates for the update.
• Multiple assignments in SET are separated by commas.
• AND means that all connected conditions must be true.
• A primary key is generally safer than a non-unique name when targeting one row.

For related data-changing operations, see the SQL UPDATE Statement reference.