SQL Update

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.

While the 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.

The SQL UPDATE statement takes a general form as below:

UPDATE <table> SET <assignment>;

The <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.