Update and Truncate Data in SQL

This article discusses how to use the Update clause in MySQL.

We can use the UPDATE statement to change the value of a column for a row or multiple rows.

Example Syntax :
UPDATE table
SET col1 = val1, col2 = val2, ... coln = valn
WHERE <condition>
ORDER BY col5
LIMIT 5;

-- The lesson queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
UPDATE Actors SET NetWorthInMillions=1;

-- Query 2
UPDATE Actors SET NetWorthInMillions=5 ORDER BY FirstName LIMIT 3;

--Query 3
UPDATE Actors SET NetWorthInMillions=50, MaritalStatus="Single";

UPDATE table SET condition;

Say Bernie Sanders wins the US Presidential elections and caps the maximum net worth for any individual to be no more than a million dollars. We’d want to update our Actor’s table in response because all the actors are now worth just one million dollars. The following query modifies the NetWorthInMillions column for all the rows in the table.

UPDATE Actors SET NetWorthInMillions=1;

UPDATE tablename SET column1=5 ORDER BY column2 LIMIT 3;

We can use LIMIT and ORDER BY in conjunction to restrict the effects of the update statement. Say, we want to increase the net worth of the first three actors, sorted by the first name, to five million dollars. We could achieve that as follows:

UPDATE Actors SET NetWorthInMillions=5 ORDER BY FirstName LIMIT 3;

The UPDATE statement has two parts. The matching phase and then the modification phase. In the first phase the row/rows that match the query are determined and in the second phase the changes are applied. If we re-execute the update query from the previous step, the count of the number of rows matched will be three but the row count for changes will be zero.

UPDATE tablename SET column1=50, column2=”Single”;

Lastly, we can update multiple columns in an UPDATE statement. Say we want to give all the actors fifty million dollars and make them single, we can do so in a single UPDATE statement.

UPDATE Actors SET NetWorthInMillions=50, MaritalStatus="Single";

How to use the Truncate clause

How to delete all the rows of a table using the TRUNCATE clause.

In the previous article, we learned how to delete data using the DELETE statement. However, if we intend to delete all the rows from a table then a faster route is to use the TRUNCATE statement. Generally, we don’t want to delete all the table rows except in the case of temporary tables. The TRUNCATE statement drops a table and recreates it for faster processing. MySQL doesn’t count the number of rows affected and may show the count to be zero or non-zero, but the number doesn’t reflect the actual number of rows affected.

Example Syntax :
TRUNCATE table;

Execute the following query to remove all the rows from the table:

TRUNCATE Actors;

Note that TRUNCATE doesn’t work with locking or transactions and is the equivalent of DELETE when used with InnoDB tables. InnoDB refers to a particular type of database engine and is covered in the lessons ahead.