We discussed the SQL query for inserting data into a database, now let’s look at all the options we have of changing, deleting, and updating that data.

Okay, so let’s say we have a field where we would like to change or Update the contents of the entire column in every row. We would need to run a query like this: (once again we’ll be using the names table from our actors database)

UPDATE names SET name= “Jodie Foster”;

Now, that would essentially change all the fields in the ‘name’ column to Jodie Foster. It may make sense to change all in a lot of situations, but not in a names table, so what if we just wanted to change one name in the table. Say for example that an employee gets married and all instances of her last name need to change , we would need to use the conditional WHERE.

So let’s say Jodie Foster gets married (yeah right , wink wink):) and changes here last name, how would we change just her last name and leave everyone else’s alone. Here’s an example of the WHERE clause which is what we’ll use.

UPDATE names SET name =”Jodie Smith”
WHERE id =”1″;

See what we did there? We told the database, where id is =1 change the value of the ‘name’ column to ‘Jodie Smith’
This would be ideal in a work situation where more than likely an employee id is the primary key used.

Okay, now let’s see an example of DELETE

Rarely will you want to delete everything in a database (unless your a malicious hacker), usually you’ll want the more targeted form of ‘sql delete from database or table” so let’s focus on more useful commands involving the DELETE query.

Once again we can rely on the WHERE conditional to help us find exactly what we want to delete.

Let’s continue with the Jodie Smith example, say we wanted to delete just her. We would run a query like this:

DELETE FROM names WHERE name =”Jodie Smith”;

This would work in this instance, but how about in a giant corp where there are thousands of employees and very many people with the same name? It’s always best to use a unique identifier such as a Primary key:

DELETE FROM names WHERE id =”1″;

Remember before deleting anything, to have a backup.