{"id":73213,"date":"2014-02-13T11:54:42","date_gmt":"2014-02-13T11:54:42","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/oracle-for-absolute-beginners-part-3-update-insert-delete\/"},"modified":"2021-07-14T13:07:35","modified_gmt":"2021-07-14T13:07:35","slug":"oracle-for-absolute-beginners-part-3-update-insert-delete","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-for-absolute-beginners-part-3-update-insert-delete\/","title":{"rendered":"Oracle for Absolute Beginners: Part 3 \u2013 Update, Insert, Delete"},"content":{"rendered":"

Part 1<\/a>, Part 2<\/a>.<\/p>\n

A wise man* once said: So no-one told you life was gonna be this way. Your job\u2019s a joke, you\u2019re broke, your love life\u2019s DOA. It\u2019s like you\u2019re always stuck in second gear. And when it hasn\u2019t been your day, you week, your month, or even your year.<\/i><\/p>\n

[*That wise man wasn\u2019t<\/em> me; but stick with me, I promise to bring this back around to Oracle SQL imminently. ]<\/p>\n

So \u2013 since it hasn\u2019t been your day, your week, your month, or even your year \u2013 you decide to go to Central Perk with your friends Ross, Rachel, Monica, Phoebe, Chandler and Joey.\u00a0 Oh, and that new friend you made that\u2019s also named Ross Geller.\u00a0 The music is loud, the atmosphere is great, and a good time is had by everyone.<\/p>\n

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\u2019d discovered that Chandler\u2019s middle name is Muriel, and that Ross\u2019 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.<\/p>\n

You roll out of bed and groggily wipe your eyes: all this new information needs recording; you\u2019d better fire up your database.<\/p>\n

UPDATE<\/h5>\n

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 \u2013 not to create<\/i> new records or to delete them \u2013 just to change them.<\/p>\n

The syntax for update statements is as follows:<\/p>\n

UPDATE <table name >\r\nSET <column name>= <value>\r\nWHERE <condition(s)>;<\/pre>\n
To see an update statement in action, log into your Oracle environment and run a query to select Chandler\u2019s middle name (middle names are stored in the MIDDLE_NAME column) from the FRIEND_NAME table. You should get a null.<\/p>\n
Now run the following update statement:<\/p>\n
UPDATE friend_name\r\nSET middle_name = 'Muriel'\r\nWHERE UPPER(first_name) = 'CHANDLER';<\/pre>\n
Done that?\u00a0 Now try running your select statement querying his middle name again.<\/p>\n
We also need to update Ross Geller\u2019s middle name to Eustace.\u00a0 Knowing our data as intimately as you do by now, can you tell me what\u2019s wrong with running the following update statement?<\/p>\n
UPDATE friend_name\r\nSET middle_name = 'Eustace'\r\nWHERE UPPER(first_name) = 'ROSS'\r\nAND UPPER(last_name) = 'GELLER';<\/pre>\n
Figured what\u2019s wrong with it yet? It\u2019s not the syntax; that\u2019s as right as rain. No, the issue \u2013 in this specific case \u2013 is that our WHERE clause identifies two<\/i> people, since we\u2019ve got two friends named Ross Geller.\u00a0 Parachute that where clause into a select query and you\u2019ll 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<\/i> Geller.<\/p>\n
Forgive me for sneaking in a lesson about primary keys when we\u2019re 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 \u2013 cos if we do not uniquely identify the records we want to change (or delete), the consequences can be critical.<\/p>\n
So, now that we\u2019ve reinforced that point, I should probably rewrite our update statement as:<\/p>\n
UPDATE friend_name\r\nSET middle_name = 'Eustace'\r\nWHERE friend_id = 1;<\/pre>\n
Let\u2019s try a couple more updates.\u00a0 It turns out Monica goes by the last name Bing and really hates being called Geller-Bing.\u00a0 She also mysteriously reveals that her middle name is \u2026 E. Just E.<\/p>\n
We could, of course, write two update statements \u2013 one for her surname and the second for her middle name.\u00a0 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:<\/p>\n
UPDATE friend_name\r\nSET middle_name = 'E',\r\nLast_name = 'Bing'\r\nWHERE friend_id = 4;<\/pre>\n
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.<\/p>\n
INSERT<\/h5>\n
Rachel has a new phone number.<\/p>\n
I don\u2019t 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.<\/p>\n
Let\u2019s add the phone number.\u00a0 She says her new number is 5550789.\u00a0 Assuming the country and area codes are unchanged and the phone id is 9, our insert statement would be as follows:<\/p>\n
INSERT INTO phone_number  (phone_id, country_code, area_code, phone_number)\r\nVALUES (9, 1, 212, 5550789);<\/pre>\n
The syntax for a basic insert statement is as follows:<\/p>\n
INSERT INTO <table_name> (<comma-separated column list>)\r\nVALUES ( <comma-separated value list>);<\/pre>\n
Got that? Good. Then maybe you should have a go.\u00a0 Last night, Phoebe mentioned that she\u2019d moved apartments (again!) and has a new address.\u00a0 It is Apartment 10, House 12, Morton Street, New York City, New York, NY 10014.<\/p>\n
Create an insert statement to put a new record (with an address_id of 4) into the ADDRESS table. You\u2019ll want to remember that you\u2019ll need to enclose VARCHAR2 strings in single quotes.<\/p>\n
DATES<\/h5>\n
And now, just to keep you on your toes, I\u2019m going to interrupt our conversation about insert statements to keep a promise I made you to tell you more about dates. (Don\u2019t panic, we\u2019ll return to inserts once this detour is over.)<\/p>\n
The problem with dates, if you recall, is how to use them in SQL; if you enclose them in single quotes they\u2019ll be taken as VARCHAR2 strings and if you don\u2019t 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\u2019ll need to use the TO_DATE function.<\/p>\n
The syntax is as follows:<\/p>\n
TO_DATE(<string>,<format mask>)<\/pre>\n
The <string> is the character string that we want converted to a date, and\u00a0 the \u00a0<format mask> is the pattern it matches.<\/p>\n
So if I wanted to use a date \u2013 say 25 March, 1999 \u2013 in a where clause or an insert statement, I might write the following:<\/p>\n
TO_DATE(’25\/03\/1999′,’DD\/MM\/YYYY’)<\/p>\n
Alternatively, if I were American, I might write<\/p>\n
TO_DATE(’03\/25\/1999′,’MM\/DD\/YYYY’)<\/p>\n
Or I could say<\/p>\n
TO_DATE(‘1999-03-25′,’YYYY-MM-DD’)<\/p>\n
You get the idea. As long as the format mask tells SQL what pattern we\u2019re using, Oracle doesn\u2019t really limit us.<\/p>\n
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\u2019d been living at the same address since 25 March, 1999, I might say:<\/p>\n
SELECT FRIEND_ID\r\nFROM FRIEND_ADDRESS\r\nWHERE MOVED_IN >= TO_DATE('25\/03\/1999','DD\/MM\/YYYY');<\/pre>\n
INSERT Cont\u2019d<\/h5>\n
Let\u2019s get back to Rachel. We were recording the fact that she has a new phone number. We\u2019ve inserted the phone number into the PHONE_NUMBER table with a phone_id of\u00a0 9; now we need to insert a row into FRIEND_PHONE, using what we\u2019ve learned about dates.<\/p>\n
INSERT INTO FRIEND_PHONE (friend_id, phone_id, start_date)\r\nVALUES (5, 9, TO_DATE('01\/01\/2014','DD\/MM\/YYYY'));<\/pre>\n
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.<\/p>\n
DELETE<\/h5>\n
The syntax for delete statements is:<\/p>\n
DELETE FROM <table name>\r\nWHERE <condition>;<\/pre>\n
Since you\u2019ve had a big bust-up with the other Ross Geller, you\u2019ve decided to delete his name from your address book.\u00a0 Using the syntax above \u2013 and remembering to use the primary key identifier instead of the name – \u00a0the statement we need is:<\/p>\n
DELETE FROM friend_name\r\nWHERE friend_id = 7;<\/pre>\n
Copy the statement, paste it in your SQL window, click the Run<\/b> button, and\u2026<\/p>\n
ERRORS<\/h5>\n
Did you get the following error?<\/p>\n
ORA-02292: integrity constraint (ALLTHINGSORACLE.FRIEND_PHONE_FRIEND_FK) violated - child record found<\/pre>\n
I\u2019m sorry; I set you up. The truth is, we cannot honestly speak about coding without talking about errors. Every programmer \u2013 no matter how good she is \u2013 runs up against errors, often quite frequently. So it is important that you learn to understand \u2013 and not fear \u2013 them.<\/p>\n
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.<\/p>\n
Our error \u2013 ORA-02292 \u2013 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.<\/p>\n
DELETE Cont\u2019d<\/h5>\n
Before we can delete Ross Geller from FRIEND_NAME, we must delete the child record from FRIEND_PHONE.\u00a0 Using his friend_id (7), why don\u2019t you write and run a delete statement to do that using the syntax we learned earlier?\u00a0 And once you\u2019ve done that, you can try deleting him from FRIEND_NAME again.<\/p>\n
CONCLUSION<\/h5>\n
And that\u2019s it. We\u2019ve 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.<\/p>\n
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.<\/p>\n
Think of what we\u2019ve learned so far \u2013 select, update, insert, delete \u2013 as our four robot lions. In the next article we\u2019re gonna join them together and build our humongous unbeatable robot of advanced selects, joins, sub-queries, functions and procedures.<\/p>\n
I. Cannot. Wait.<\/p>\n
PRACTICE<\/h5>\n
Here are a few teasers to keep you busy until next time.<\/p>\n