SQL UPDATE Statement – Modifying Table Records

We can manipulate, modify or update records in a database table using a SQL UPDATE statement. Specifying a WHERE clause is extremely important for filtering data in a sql statement so that we update the appropriate records in a table.

The generic update statement in SQL can be used universally between databases as we see in the syntax below.

UPDATE <table_name> SET <column_name> = ‘new_value’ WHERE <column> = ‘filter_value’;

We can see a practical example of applying an update to an employee name. Similarly, we could apply updates in order to correct misspelled names and bad character entries.

SQL syntax differences become more established when our updates begin to include more complex logic. As a result, using sub-queries can differ between updates depending on the database being used. Be sure to reference specific database documentation to ensure proper syntax when applying subqueries.

Multiple Columns

An UPDATE statement can also be used to update multiple columns in SQL. A common mistake is to add the SET keyword to each column line. Make sure to only include SET in the first line.

UPDATE <table_name>
SET <column_name_1> = ‘new_value’ ,
<column_name_n…> = ‘new_value’
WHERE <column> = ‘filter_value’; — Updating multiple columns in SQL

We can also use a practical example to fill in default values of ‘Uknown‘ where we have null fields for our employee records.

Be Careful & Create A Table Copy

The UPDATE statement is considered a part of the DML language since it’s used in SQL to manipulate the data in a table. To re-iterate, it’s important to ensure we have the proper filtering included in our query. Otherwise, we may be likely to update records in our table that we did not intend to.

One way to safe-guard ourselves from making mistake when updating a table is to create a copy first. As a result, our table copy can give us a quick backup in case we made a mistake in our update process. Just be sure to delete the extra tables afterward as a best practice.