Bright Java Tutorial

is brought to you by DoITBright LLC

MySQL

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.
  INSERT INTO ACCOUNT(EMAIL, CREATE_DATE) 
      VALUES ('new_cust@testmail.com', CURDATE());
                            
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.
  SELECT * FROM ACCOUNT;
                            
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 'new_cust@testmail.com'. 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 ORDERED_ITEM 
      (DESCRIPTION, PRICE, ORDER_DATE, ACCOUNT_ID) 
      VALUES ('12 Piece Crayons', 2.50, CURDATE(), 6);
                            
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 ORDERED_ITEM_ID.
  SELECT * FROM ORDERED_ITEM;
                            
For example, the value of the ORDERED_ITEM_ID is 3. This is how our UPDATE statement will look like.
  UPDATE ORDERED_ITEM 
    SET DESCRIPTION = '24 Piece Crayons', PRICE = 4.5, 
      ORDER_DATE = '2016-09-13' 
    WHERE ORDERED_ITEM_ID = 3;
                            
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.

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.


Back