SQL 3: DELETE & UPDATE: A Change Will Do You Good

Why not to rely the last record is the most up to date?

Human error Entered data at the same time - duplicates Hope

INSERT

INSERT INTO clown_info VALUES ('Mr. Hobo', 'Eric Gray\'s Party', 'cigar', 'black hair', 'tiny hat', 'violin'); INSERT INTO clown_info VALUES ('Greyhound', 4, 14, 'Gold', 'Y', 50) AND ('Blackthorn', 3, 8.4, 'Gold', 'Y', 33); DELETE FROM drink_info WHERE color = 'yellow'; You can use the DELETE FROM in order to delete the drink_name listed in the table along with the entire row in the yellow color. You may do this with the UPDATE. Figured.

DELETE with the clauses

In addition, you can delete values with the LIKE, IN, BETWEEN, and all the conditions. You can delete/ clean up duplicated records by using the JOIN clause.

SELECT - INSERT-DELETE three step

SELECT * FROM WHERE INSERT INTO VALUES DELETE FROM WHERE Mandatory: Proceed the select, insert, delete three step rather than the insert and delete two step.

SET

This keyword belongs in an UPDATE statement and is used to change the value of an existing column.

Table Design

You can't guarantee the last row is the most recent data because.... -you can modify the table by changing the order of the values. -two individuals can split the data entry to work at the same time. -duplicate records should never exist at a table. *You can avoid duplicated values with good table design. In this case, you can add a column with the current time and date to keep track of clowns movement. In addition, in SQL most of the time you fix other people's messes. This is very common.

What to do when you want a history of movements of the clowns?

Add current time and date column to talk

UPDATE

Change the data UPDATE your_table SET column_name = 'newvalue' WHERE column_name = 'valued'; UPDATE doughnut_ratings SET type = 'glazed' WHERE location = 'Krispy King'; This statement is specifying the where to update the record (glazed). Glazed is a record under the type column. UPDATE doughnut_ratings SET type = 'glazed' WHERE type = 'plain glazed'; This statement updates the type of doughnut where is located and overrides the plain glazed. The WHERE clause can help specify the value in the column name because it tests if it is true. The statement UPDATEs an existing column(s) with a new value in SET in specifying with the WHERE command. With the UPDATE SET, you update a single row or multiple rows without specifying the column(s) by leaving out the WHERE clauses.

DELETE

DELETE This is your tool for deleting rows of data from your table. Use it with a WHERE clause to precisely pinpoint the rows you want to remove. DELETE name FROM clown_info WHERE name = 'Zippo' AND last seen = 'milestone mall' AND last_seen = 'Tracy\'s' AND last_seen = 'Ball-mart'; You can use DELETE to delete a single row or multiple rows depending on the WHERE clause. You cannot delete a value from a single column or tableful of columns. Just rows. Note, In order to DELETE, the clauses in the statement must be true. DELETE FROM clown_info WHERE name = 'Mr. Hobo' AND last_seen = 'Tracy\'s'; Deletes the entire row from the record specified in Mr. Hobo and Tracy's. DELETE name FROM clown_info WHERE name = 'Mr. Hobo' AND last_seen = 'Tracy\'s'; The statement above is more accurate and precise. DELETE FROM clown_info WHERE name ='Mr. Hobo' AND last_seen <> 'Tracy\'s'; The <> is equivalent to the operator of the equal sign. DELETE FROM your_table You delete every row from the table

INSERT-DELETE two step

If needed you can SELECT the information with the WHERE clause. 1. You use the INSERT to add the information, including the old information. INSERT INTO clowns_info VALUES ('Clarabelle', 'Belmont Senior Center', 'F, pink hair, huge flower, blue dress', 'dancing'); 2. Then, DELETE the old record using the WHERE clause. DELETE FROM clown_info WHERE activities = 'yelling, dancing' AND name = 'Clarabelle'; The two-step below is from the page 139. INSERT INTO drink_info VALUES ('Oh My Gosh', 4.5, 8.6, 'orange', 'Y', 35); DELETE FROM drink_info WHERE cost = 3.5; INSERT INTO drink_info VALUES ('Blue Moon', 3.50, 3.20, 'blue', 'Y', 12), ('Lime Fizz', 3.50, 5.40, 'green', 'Y', 24); DELETE FROM drink_info WHERE cost = 2.5; THE BONUS: INSERT INTO drink_info VALUES ('Oh My Gosh', 4.5, 8.6, 'orange', 'Y', 35), ('Blue Moon', 3.50, 3.20, 'blue', 'Y', 12), ('Lime Fizz', 3.50, 5.40, 'green', 'Y', 24); DELETE FROM drink_info WHERE cost = 2.5 AND WHERE cost = 3.5; Tip: Make sure to think about what you are doing before querying. You do not want to accidentally delete data from your table. That being said, you want to visualize the process as stated above. My honest response is below and I still query the correct response. INSERT INTO drink_info VALUES ('Blue Moon', 3.50, 3.2, 'blue', 'Y', 12), ('Lime Fizz', 3.50, 5.40, 'green', 'Y', 24) DELETE FROM drink_info WHERE cost = 2.50; INSERT INTO drink_info VALUES ('Oh My Gosh', 4.50, 8.60, 'orange', 'Y', 35) DELETE drink_name FROM drink_info WHERE drink_name = 'Oh My Gosh' AND cost = 3.50; You want to extract the data to store in order to collect the data to insert new information. DELETE FROM drink_info WHERE cost = 2.50 AND cost = 3.5; INSERT INTO drink_info VALUES ('Blue Moon', 3.50, 3.20, 'blue', 'Y', 12), ('Lime Fizz', 3.50, 5.40, 'green', 'Y', 24), ('Oh My Gosh', 4.5, 8.6, 'orange', 'Y', 35);

UPPER () LOWER ()

This will set the characters in the table to upper case or lower case. SELECT UPPER (string); SELECT LOWER (string);

UPDATE with mathematical operators

UPDATE drink_info SET cost = cost + 1 WHERE drink_name = 'Blue Moon'; This statement will automatically UPDATE data to add an extra dollar. UPDATE statements can be used on multiple records in your table. Use them with basic math operators to manipulate your numeric values: multiplication, division, subtraction, and addition

How do you UPDATE when you have more than one columns?

UPDATE your_table SET first_column = 'newvalue', second_column = 'another_value'; You can use UPDATE to update a single row or multiple rows, depending on the WHERE clause. You can add a comma in order to set the column names. in adding new columns. You do not need to add more SET clauses for additional column names. do you automatically add columns in the set. This statement does not have the WHERE clause, every column specified in the SET clause for all records in the table will be updated with the new value.