---[REF]---"style":"heading"---[REF]--- ---[SIDEBAR]--- "type":"objectives", "title":"Learning Objectives", "text":"After completing this lesson, the successful student will be able to use SQL statements to:

1. Make changes to existing data in a table.
2. Delete rows from table.

" ---[SIDEBAR]--- This lesson describes the process of changing the data currently stored in database tables. You can use SQL statements to edit specific values on a table. You can also delete rows stored on the table. Both of these processes make permanent changes to the tables and should be done with care. Because of this, your database administrator will likely limit those who are able to execute these statements. You may need to ask for permission to be able to execute these queries. Since all of the examples in this lesson will make changes to database tables, it is a good idea to make a copy of a table so that the original data is not affected. **Execute these SQL statements to create copies of the work and museum tables for this lesson.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:museum-create-work-museum-copies ---[SQL]--- CREATE TABLE museum_copy AS SELECT * from museum; CREATE TABLE work_copy AS SELECT * from work; ---[SQL]--- ---[SIDEBAR]--- "type":"note", "title":"SQLite and Changing Data", "text":"If you are using the SQLite option for this lesson, the changes you make to the data will persist until you refresh or leave this page." ---[SIDEBAR]--- ## Updating Data To edit the values stored in a table you will use the keyword UPDATE. Update statements can be writen to change the values on a table for a specific column or to change the values on multiple columns. The general syntax for an UPDATE statement is: UPDATE *tablename* SET *column* = *updated value* ---[SIDEBAR]--- "type":"term", "title":"UPDATE", "text":"Keyword used to create a SQL statement that changes data in a table." ---[SIDEBAR]--- ---[SIDEBAR]--- "type":"term", "title":"SET", "text":"Keyword used in an UPDATE statement to specify the column to be changed and the value it should be changed to." ---[SIDEBAR]--- Suppose you want to change the width of all of the works of art (on the work_copy table you created above) to be 10 inches. The UPDATE statement that accomplishes this is: UPDATE work_copy SET width = 10; **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:work-update-width ---[SQL]--- UPDATE work_copy SET width = 10; SELECT * from work_copy; ---[SQL]--- Notice that all of the values for width on the work_copy table are now 10. This is probably not very useful (since each work of art is a different size), but it does demonstrate how the UPDATE statement works. It is likely more useful to change the values by the same factor (scale by the same amount) or algorithm (add the same amount to each) rather than set them to the same number. For example, you may want to modify the values for teh height of each work of art on the work_copy table to be twice the original height. This UPDATE statement will accomplish that: UPDATE work_copy SET height = height * 2; **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:work-update-height ---[SQL]--- SELECT * from work_copy; UPDATE work_copy SET height = height * 2; SELECT * from work_copy; ---[SQL]--- The additional SELECT query before the update is there to show the values for height on the work_copy table before and after the values are changed. The values for height are doubled by the UPDATE statement. Note the syntax of the SET clause. The value for height (left side of the equals sign) for each row will receive the calculated value on the right side of the equals sign (2 times the current value for the height). In these examples, you modified all of the values for the width and height columns of the table. This is usually not a good idea. It is better to be more targetted, updating the values for specific rows of a table. This is done using a WHERE clause. For example, you may want to update the values for country on the museum_copy table to 'United States' (without the quotes) for the museums located in the USA. The syntax for this UPDATE statement is: UPDATE museum_copy SET country = 'United States' WHERE country = 'USA'; **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:museum-update-usa-united-states ---[SQL]--- UPDATE museum_copy SET country = 'United States' WHERE country = 'USA'; SELECT * from museum_copy; ---[SQL]--- ---[SIDEBAR]--- "type":"caution", "title":"UPDATE Statements without a WHERE Clause", "text":"Given how easy it is to change data in a table with an UPDATE statement and that these changes are permanent, it is almost always a good idea to use a WHERE clause to target the row or rows you wish to change." ---[SIDEBAR]--- You can also use the WHERE clause to change a column value for a specific row on a table. Suppose you want to change the city for museum 69 (Saint Louis Art Museum) from St. Louis to Saint Louis. The syntax for this UPDATE statement is: UPDATE museum_copy SET city = 'Saint Louis' WHERE museum_id = 69; **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:museum-update-saint-louis ---[SQL]--- UPDATE museum_copy SET city = 'Saint Louis' WHERE museum_id = 69; SELECT * from museum_copy WHERE museum_id = 69; ---[SQL]--- Additionally, you can change the value for more than one column using the same UPDATE statement. For example, you may wish to change the name and the city for museum 69 (Saint Louis Art Museum) to be "St. Louis" instead of Saint Louis. The syntax for this would be: UPDATE museum_copy SET name = 'St. Louis Art Museum', city = 'St. Louis' WHERE museum_id = 69; **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:museum-update-saint-louis ---[SQL]--- UPDATE museum_copy SET name = 'St. Louis Art Museum', city = 'St. Louis' WHERE museum_id = 69; SELECT * from museum_copy WHERE museum_id = 69; ---[SQL]--- Notice the syntax of the SET clause. There is only one SET clause in the statement, but two values will be changed. Each of the changes are specified with a comma separating the changes. You can change any number of values using a single SET clause separating the requested changes with commas. ## Deleting Data You can write a SQL statement that removes data from a table. The keyword DELETE is used to remove rows from a table. You can write a DELETE statement that removes specific rows from a table or removes all of the data from a table. The general syntax for a DELETE statement is: DELETE FROM *tablename* *optional WHERE clause to target specific rows for deletion* ---[SIDEBAR]--- "type":"term", "title":"DELETE", "text":"Keyword used in a SQL statement to remove rows from a table." ---[SIDEBAR]--- ---[SIDEBAR]--- "type":"note", "title":"Removing Rows Versus Removing Values", "text":"It is important to recognize that DELETE will remove an entire row from a table. If you want to delete a value from one of the columns in a row, you would use UPDATE and SET the value for that column to NULL." ---[SIDEBAR]--- Suppose you want to remove all of the museums from the museum_copy table that are located in France. The syntax for this DELETE statement is: DELETE FROM museum_copy WHERE country = 'France'; **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:museum-delete-french ---[SQL]--- DELETE FROM museum_copy WHERE country = 'France'; SELECT * from museum_copy; ---[SQL]--- After the DELETE statement executes there will not be able museums on the museum_copy table that are located in France. Just as with the UPDATE statement, it is possible to use DELETE to remove all of the rows in a table. To do this, omit the WHERE clause in the DELETE statement. ---[SIDEBAR]--- "type":"note", "title":"DELETE Versus DROP", "text":"---[REF]---"style":"link","pageId":"lesson-insert"---[REF]--- described the process for removing a complete table (table and data) from a database. This is done using the keyword DROP. The keyword DELETE removes only the data from a table. Even if you remove all of the data from a table using a DELETE statement, the table will remain (without data) in the database." ---[SIDEBAR]--- Suppose you are unhappy with the changes you made to the width and height columns of the work_copy table. You decide you want to remove all of the data from that table. The DELETE statement that accomplishes this is: DELETE FROM work_copy; **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:work-delete-all ---[SQL]--- DELETE FROM work_copy; SELECT * from work_copy; ---[SQL]--- Once the DELETE statement executes, all of the data on the work_copy table is removed. This happens because there is not a WHERE clause to target specific rows for deletion. ---[SIDEBAR]--- "type":"caution", "title":"DELETE Statements", "text":"The DELETE statement is easy to use and it is permanent. Caution should always be used when you remove date using a DELETE statement. It is almost always a good idea to include a WHERE clause in a DELETE statement to target the specific rows you want to remove." ---[SIDEBAR]--- ## Summary This lesson discuss how to use SQL statement to make changes to the data in a table. UPDATE statements are used to edit values for columns in a table. DELETE statements are used to remove rows from a table. ---[SIDEBAR]--- "type":"tip", "title":"WHERE Conditions", "text":"The point was made repeatedly in this lesson to use WHERE clause conditions with UPDATE and SELECT statements to target specific values and rows to modify or remove. It is usually helpful to try a WHERE condition in a SELECT statement to ensure that it targets the data you wish to change or delete. Once you are satisfied that a condition identifies the correct rows, it is ready to be used in an UPDATE or DELETE statement." ---[SIDEBAR]---