w1coding.comAll tutorials

SQL Update

SQL lessons
  1. Environment Setup
  2. Introduction
  3. Select
  4. Where
  5. Decision
  6. Insert
  7. Delete
  8. Distinct
  9. Limit
  10. Operator
  11. In
  12. Join
  13. Leftjoin
  14. Rightjoin
  15. Fulljoin
  16. Groupby
  17. Having
  18. Order
  19. Update

The SQL UPDATE statement changes values in existing table rows. Use SET to specify the new values and WHERE to select the rows to change.

Basic syntax

UPDATE table_name
SET column_name = new_value
WHERE condition;

Without a WHERE condition, the database attempts to update every row in the table. Check the intended records before running the statement.

Check the row first

Suppose an Employees table stores an employee identifier, department and salary. This query previews the employee whose identifier is 7:

SELECT employee_id, department, salary
FROM Employees
WHERE employee_id = 7;

Update one value

UPDATE Employees
SET salary = 70000
WHERE employee_id = 7;

The condition selects employee 7. The assignment changes that employee's salary to 70,000. Rows that do not match the condition remain unchanged.

Update multiple columns

UPDATE Employees
SET department = 'Science', salary = 75000
WHERE employee_id = 7;

Separate assignments with commas. Text values use single quotation marks, while numeric values usually do not. A condition can match more than one row, so review the expected number of affected records.

Verify the change

Run the preview query again to check the stored values. For important changes, use a transaction when supported by your database and storage engine. Commit only after verification; roll back if the results are wrong.

Practice task

In a practice database, create a Products table containing a product identifier, name and price. Preview one product, update its price using its identifier, then select it again. Explain what would happen if you removed the WHERE clause.

Application safety

Applications should use parameterised statements rather than concatenate user input into SQL. Grant only the database permissions the application needs, and keep a backup before large changes.