DML data manipulation language the three main things in DML are inserting updating and deleting rows and so we’ll cover those topics and then we’re going to talk about transactions that make your changes permanent or remove your changes I’m going to talk about how you control transactions with the commit or rollback and who sees what when you make a change to other people see it or not when do they see it that’s sort of thing okay main concepts here DML select it I don’t consider it truly a DML but it’s often included as DML it doesn’t change the data it really it does data manipulation and that it pulls it out but it doesn’t change the data most of what you guys do our selects and that’s what we focused on everything every chapter up to now has been self-selecting now we’re going to do the inserts updates and deletes there is also emerged arm that emerged statement that is a combination of insert update and deledeletesed on different conditions inserts this row.
If this is true else update the row else delete the row so it’s a fact that kind of combination so we’ll talk about DML we’re also going to talk about truncate which manipulates data but is not a DM else command it is a DDL DDLmand is considered dated definition truncate actually you know it’s funny how they do that because truncate is the same similar to delete only delete says which rose do you want to delete and could delete all rows in a table but doesn’t have to truncate every rose on the table but truncate is actually a ddl DDLmand, okay and we’ll talk about the ramifications to that in this chapter it’s not a DML okay so let’s look at the syntax the insert update and the lead and merge are all different all rt in size two options here and insert can insert one row at a time using a vavaluesse or with a completely different syntax.
It can insert many rows at a time using an insert into with a select case this is not a select sub query don’t get this wrong you know we talked about sub queries in / ends this is a special syntax insert into a table select star from another table noper ends around it’s not a sub-query it looks like a sub-query but it’s not so be careful that’s a gotcha update in the lead can so insert can insert a single row by specifying the columns for that for that row or insert multiple rows if those rows are in another table because we’re saying insert into one table select from another table or tables update and delete can update one row a thousand rows a million rows 10 rows as many roses you want the number of rows updated and deleted depends on the where clause if I say delete from customers with know where clause every row in the customers table is deleted be really careful it’s an easy one to make a mistake on you have to say delete from customers where custo we equal 1 2 3 4 5 it deletes that one row.
If I said delete from customers where state equal New York it deletes all the customers in New York State if I say delete from customers the worst ad called Hawaii and you have no customers in Hawaii it deletes no rose it still works doesn’t matter how many rows you update the lead insert they’re going to work as long as the syntax is right in and all the rules are followed and merged as I said can insert update and delete based on conditions and the advantage of a merge is that let’s say you’ve got a bunch of rows some have to be inserted well you can do that with an insert into with the select some have to be updated you can do in our that with an update so I have to been dbeeneted you could do that with three different statements do some insert of the right rose updated right rose to lead the er to write rose or you can do it with a merge which is a single pass of your data.
I’ve got all these rows some are inserted somewhere updated some were deleted I could do it in three statements but it has to pass those rows three times as to process them three times to figure out which ones are inserted updated or deleted with emerge it only has to process those rows once so it’s pastor that’s the idea okay we’re not going to cover too much of the merge statement, so generally a more advanced tool okay if you do insert update delete or merge their old EML and to make the changes permanent you must do a commit and just like any other sequel commands with a semicolon commit semicolon makes everything permanent if you don’t want to make a permanent you want to remove all your changes you do a rollback semicolon now it commits everything that you’ve done since the last commit or rollback it rolls back everything you’ve done since the last commit or rollback it doesn’t roll back everything you’ve done since you saw you gone to the database just since the last time you did it come in.
So you make some changes you do a commit it’s in the database make some different changes who will commit it’s in the database make some different changes to a rollback it only rolls back those different changes but the first toots two sets the changes you made with the commits they made a permanent already now let’s look at a single statement and insert and update and delete they could insert update delete many rows, not just one row as many rows as you want that’s a statement a still or nothing meaning let’s say you’re trying to insert ten rows by selecting from another table and the primary ki as we know has to be unique for every row well what if nine of those rows were good and go in, but one row already has that record in there that customer number is already in well you can’t insert a duplicate customer number if that’s the primary key so that one row will fail but if any anyone fails.
Since it’s all done in a single insert statement they all fail it’s all or nothing if any row fails even if then there Carillion rows and only one fails they all fail none of them go in that has nothing to do with your commit or rollback that’s a single statement will fail so if I did some inserts they work I did some more inserts they worked I did a MOA more insert a million rows that fail well the first two are still in there that that one statement failed those million rows failed if I did a million a mi million the first two are good they go in there’s two million that are in the table the third million there’s one row that failed none of those third million go in only two million are in I could then commit those two million if I wanted it doesn’t remove everything like a rollback does it just says everything in this single statement fails.
There is an exception to this rule that all or nothing and that’s a more advanced feature called Oracle DML error logging you can actually turn on error logging and allow the good rose to go in, and the bed rose to fail and identify the bed rose and that’s a more advanced feature.