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_nameSET middle_name = 'Muriel'WHERE UPPER(first_name) = 'CHANDLER';
We also need to update Ross Geller’s middle name to Eustachian. 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_nameSET middle_name = 'Eustace'AND UPPER(last_name) = 'GELLER';
WHERE UPPER(first_name) = 'ROSS'
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_nameSET 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:
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.
UPDATE friend_nameSET 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.INSERTRachel 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.
DATES
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:
TO_DATE(<string>,<format mask>)
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:
TO_DATE(’25/03/1999′,’DD/MM/YYYY’)
Alternatively, if I were American, I might write
TO_DATE(’03/25/1999′,’MM/DD/YYYY’)
Or I could say
TO_DATE(‘1999-03-25′,’YYYY-MM-DD’)
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_IDFROM FRIEND_ADDRESSWHERE MOVED_IN >= TO_DATE('25/03/1999','DD/MM/YYYY');
INSERT Cont’d
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.
DELETE
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_nameWHERE friend_id = 7;
Copy the statement, paste it in your SQL window, click the Run button, and…
ERRORS
Did you get the following error?ORA-02292: integrity constraint (ALLTHINGSORACLE.FRIEND_PHONE_FRIEND_FK) violated - child record foundI’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.
DELETE Cont’d
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.