Update Statement In MySQL
The purpose of UPDATE statement is to update existing records in a database table.
For example, you found a website selling school and office supplies. You decided to register and
you placed an order of 12 Piece Crayons for the price of 2.5. You suddenly
remembered that you wanted a 24 Pice Crayons instead. Good thing, the website where you
placed your order allows updating of orders. Here is how it will look like in MySQL.
You registered for an account. This will be an INSERT statement.
We discussed this in the previous lesson.
Now that we have signed up for an account, we can now place an order. This will
also be an INSERT statement. To accomplish this,
we will need the ACCOUNT_ID for our account. I know we have not yet discussed
SELECT statement yet so please forgive me. We will do that in the next lesson.
The above statement will return all the existing records in the ACCOUNT table. Look for the
row where you can see the EMAIL column that has a value of 'firstname.lastname@example.org'. Take note of the
value for the ACCOUNT_ID for that row. Let us say, the value for the ACCOUNT_ID is 6. It is now time
to perform the INSERT statement for the item to be ordered.
INSERT INTO ACCOUNT(EMAIL, CREATE_DATE)
VALUES ('email@example.com', CURDATE());
However, we realized that we want a 24 Piece Crayons instead. We updated our order.
This will be an UPDATE statement. One of the best way to do this is by using the
ORDERED_ITEM_ID as a condition for the UPDATE statement. So, let us find out the
INSERT INTO ORDERED_ITEM
(DESCRIPTION, PRICE, ORDER_DATE, ACCOUNT_ID)
VALUES ('12 Piece Crayons', 2.50, CURDATE(), 6);
For example, the value of the ORDERED_ITEM_ID is 3. This is how our UPDATE statement will
SELECT * FROM ORDERED_ITEM;
The above UPDATE statement means, update records from the table named as
'ORDERED_ITEM'. The columns that we wish to perform updates on are
'DESCRIPTION', 'PRICE' and 'ORDER_DATE'. Set these columns to values
24 Piece Crayons', 4.5 and '2016-09-13' respectively. However, only update those
rows where the column ORDERED_ITEM_ID has a value of 3.
SET DESCRIPTION = '24 Piece Crayons', PRICE = 4.5,
ORDER_DATE = '2016-09-13'
WHERE ORDERED_ITEM_ID = 3;
If you forgot to include the WHERE clause as part of the UPDATE statement, all
rows or records from the table 'ORDERED_ITEM' will be updated with the same data.
This is not good. So be very careful. Use the SQL UPDATE statement with caution.