Sometimes it may be desired to not return records as the result of a query, or add new ones via an
INSERT statement, but instead to modify the values of existing data in a table. This can be accomplished using the
UPDATE statement in SQL.
UPDATE SQL command may only occasionally be used by a Data Analyst or Data Scientist in an ad hoc fashion, it is far more likely to appear in transactional SQL code or in that for data engineering work. Nonetheless, a savvy analyst should be able to read and understand what a SQL
UPDATE statement is doing.
UPDATE statement takes a general form as below:
UPDATE <table> SET <assignment>;
<assignment> part of the command is what modification the SQL engine applies to the data when the
UPDATE statement is executed. It can be used to set all values in a column to be a single scalar value, for example, to set the gender of all employees in the
employees table in to ‘F’:
UPDATE employees SET gender = ‘F’;
More often, the assignment part of the statement could take a SQL expression which might depend on other data.
For example, let’s say we wanted to (arbitrarily) double each employee number. The assignment could now use the
emp_no column in an expression and
UPDATE statement would set the returned value of the expression for each row:
UPDATE employees SET emp_no = emp_no*2;
While here we have seen the
UPDATE statement being applied to table columns in their entirety, more often an
UPDATE statement would be combined with a
WHERE clause to only modify data in a table for records fulfilling certain criteria or according to specific business logic.
Learn SQL Today
Get hands-on experience writing code with interactive tutorials in our free online learning platform.
- Free and fun
- Designed for beginners
- No downloads or setup required