A wise man* once said: So no-one told you life was gonna be this way. Your job’s a joke, you’re broke, your love life’s DOA. It’s like you’re always stuck in second gear. And when it hasn’t been your day, you week, your month, or even your year.
[*That wise man wasn’t me; but stick with me, I promise to bring this back around to Oracle SQL imminently. ]
So – since it hasn’t been your day, your week, your month, or even your year – you decide to go to Central Perk with your friends Ross, Rachel, Monica, Phoebe, Chandler and Joey. Oh, and that new friend you made that’s also named Ross Geller. The music is loud, the atmosphere is great, and a good time is had by everyone.
You wake up the next morning with a bit of a sore head and try to recall everything that went on the night before: you’d discovered that Chandler’s middle name is Muriel, and that Ross’ is Eustace. Rachel gave you her new phone number. Phoebe gave you her new address. Oh, and you had a big fight with new Ross and decided you no longer want to be friends with him.
You roll out of bed and groggily wipe your eyes: all this new information needs recording; you’d better fire up your database.
Since we thoroughly looked into SELECT statements in the previous part, we can now turn our attention to UPDATE statements. In SQL we use update statements to change existing records – not to create new records or to delete them – just to change them.
The syntax for update statements is as follows:
UPDATE <table name > SET <column name>= <value> WHERE <condition(s)>;
To see an update statement in action, log into your Oracle environment and run a query to select Chandler’s middle name (middle names are stored in the MIDDLE_NAME column) from the FRIEND_NAME table. You should get a null.
Now run the following update statement:
UPDATE friend_name SET middle_name = 'Muriel' WHERE UPPER(first_name) = 'CHANDLER';
Done that? Now try running your select statement querying his middle name again.
We also need to update Ross Geller’s middle name to Eustace. Knowing our data as intimately as you do by now, can you tell me what’s wrong with running the following update statement?
UPDATE friend_name SET middle_name = 'Eustace' WHERE UPPER(first_name) = 'ROSS' AND UPPER(last_name) = 'GELLER';
Figured what’s wrong with it yet? It’s not the syntax; that’s as right as rain. No, the issue – in this specific case – is that our WHERE clause identifies two people, since we’ve got two friends named Ross Geller. Parachute that where clause into a select query and you’ll see that it returns more than one record. However, we only want to update one record, since we only have one friend named Ross Eustace Geller.
Forgive me for sneaking in a lesson about primary keys when we’re talking about update statements, but this is why it is doubly important to use primary key columns in your where clauses when updating (or deleting) records – cos if we do not uniquely identify the records we want to change (or delete), the consequences can be critical.
So, now that we’ve reinforced that point, I should probably rewrite our update statement as:
UPDATE friend_name SET middle_name = 'Eustace' WHERE friend_id = 1;
Let’s try a couple more updates. It turns out Monica goes by the last name Bing and really hates being called Geller-Bing. She also mysteriously reveals that her middle name is … E. Just E.
We could, of course, write two update statements – one for her surname and the second for her middle name. However, with SQL it is possible to update more than one column in a single update statement as long as the columns are in the same table. We simply need to separate the columns in the SET clause with a comma. Let me demonstrate:
UPDATE friend_name SET middle_name = 'E', Last_name = 'Bing' WHERE friend_id = 4;
This method works just as well for three, four or however many columns; do bear in mind, however, that SQL will use the same WHERE clause to identify the record(s) to update.
Rachel has a new phone number.
I don’t know how well you remember our database structure, but to record this information we will need to add the new number to the PHONE_NUMBER table (this will call for an INSERT statement), and then add a record (another INSERT) to the FRIEND_PHONE linking this new number with Rachel.
Let’s add the phone number. She says her new number is 5550789. Assuming the country and area codes are unchanged and the phone id is 9, our insert statement would be as follows:
INSERT INTO phone_number (phone_id, country_code, area_code, phone_number) VALUES (9, 1, 212, 5550789);
The syntax for a basic insert statement is as follows:
INSERT INTO <table_name> (<comma-separated column list>) VALUES ( <comma-separated value list>);
Got that? Good. Then maybe you should have a go. Last night, Phoebe mentioned that she’d moved apartments (again!) and has a new address. It is Apartment 10, House 12, Morton Street, New York City, New York, NY 10014.
Create an insert statement to put a new record (with an address_id of 4) into the ADDRESS table. You’ll want to remember that you’ll need to enclose VARCHAR2 strings in single quotes.
And now, just to keep you on your toes, I’m going to interrupt our conversation about insert statements to keep a promise I made you to tell you more about dates. (Don’t panic, we’ll return to inserts once this detour is over.)
The problem with dates, if you recall, is how to use them in SQL; if you enclose them in single quotes they’ll be taken as VARCHAR2 strings and if you don’t they might be mistaken for numbers. The trick is to take a character string and tell SQL to convert it to a date; to do that we’ll need to use the TO_DATE function.
The syntax is as follows:
The <string> is the character string that we want converted to a date, and the <format mask> is the pattern it matches.
So if I wanted to use a date – say 25 March, 1999 – in a where clause or an insert statement, I might write the following:
Alternatively, if I were American, I might write
Or I could say
You get the idea. As long as the format mask tells SQL what pattern we’re using, Oracle doesn’t really limit us.
Let me show you how we might use the to_date function in a select statement. If I wanted a list of all my friends who’d been living at the same address since 25 March, 1999, I might say:
SELECT FRIEND_ID FROM FRIEND_ADDRESS WHERE MOVED_IN >= TO_DATE('25/03/1999','DD/MM/YYYY');
Let’s get back to Rachel. We were recording the fact that she has a new phone number. We’ve inserted the phone number into the PHONE_NUMBER table with a phone_id of 9; now we need to insert a row into FRIEND_PHONE, using what we’ve learned about dates.
INSERT INTO FRIEND_PHONE (friend_id, phone_id, start_date) VALUES (5, 9, TO_DATE('01/01/2014','DD/MM/YYYY'));
We also need to insert a row into FRIEND_ADDRESS to record the fact that Phoebe has a new address. Her friend_id is 6 and the address_id is 4. Using any date of your choosing in the MOVED_IN column, write the insert statement.
The syntax for delete statements is:
DELETE FROM <table name> WHERE <condition>;
Since you’ve had a big bust-up with the other Ross Geller, you’ve decided to delete his name from your address book. Using the syntax above – and remembering to use the primary key identifier instead of the name – the statement we need is:
DELETE FROM friend_name WHERE friend_id = 7;
Copy the statement, paste it in your SQL window, click the Run button, and…
Did you get the following error?
ORA-02292: integrity constraint (ALLTHINGSORACLE.FRIEND_PHONE_FRIEND_FK) violated - child record found
I’m sorry; I set you up. The truth is, we cannot honestly speak about coding without talking about errors. Every programmer – no matter how good she is – runs up against errors, often quite frequently. So it is important that you learn to understand – and not fear – them.
Oracle errors usually begin with an ORA-, followed by a numeric code and a description. If you find the description to be inadequate, try googling the error code, there are numerous sites where you can plug in the error number and receive advice on what to do next.
Our error – ORA-02292 – tells us that an integrity constraint has been violated because a child record has been found. What this means is that we cannot delete Ross from our FRIEND_NAME table when there are records that depend on him (child records) in another table.
Before we can delete Ross Geller from FRIEND_NAME, we must delete the child record from FRIEND_PHONE. Using his friend_id (7), why don’t you write and run a delete statement to do that using the syntax we learned earlier? And once you’ve done that, you can try deleting him from FRIEND_NAME again.
And that’s it. We’ve covered the basics; we know how to select, update, insert and delete. Plus, we know how to manipulate the various data types and how to stare down error messages.
When I was a child there was a cartoon series on TV named Voltron: Defender of the Universe in which there were 4 robot lions who, individually, were pretty good fighters. But when things got tough they would come together to form one giant robot who was pretty much undefeatable.
Think of what we’ve learned so far – select, update, insert, delete – as our four robot lions. In the next article we’re gonna join them together and build our humongous unbeatable robot of advanced selects, joins, sub-queries, functions and procedures.
I. Cannot. Wait.
Here are a few teasers to keep you busy until next time.
- Since we’ve added a new phone number for Rachel (friend_id 5) we need to update our records to indicate that she’s no longer using her old phone number. Update FRIEND_PHONE to do this.
- Since Phoebe (friend_id 6) has moved to a new address, we need to update our records to indicate that she no longer lives at her old address. Update FRIEND_ADDRESS to do this.
- Try inserting the details of your real-world friends into the FRIEND_NAME table. Remember that each record must have a unique friend_id.
- Insert some phone numbers into the PHONE_NUMBER table, and then insert records into FRIEND_PHONE associating them with your friends, ensuring that you put dates in the START_DATE column.
- Delete some of the records you have just added.