Oracle sql updating multiple tables
This is one of the most interesting questions I keep on getting on this email and I find that not everyone knows about it. Our requirement is that we have Table2 which has two rows where Col1 is 21 and 31.
Sometimes, we find that a piece of data that we did not maintain becomes important, and we need to add a new table column to the database.
You can wrap your around your more than one UPDATE statement in a single transaction, this way if one or more statement fails, the entire transaction rolls back.
Here is the sample example of how the transaction should work with multiple update statements.
Finally, I came up with a solution that works very efficiently, even with my large 1,000,000-record table.
-- Best practice update ( select m.invoice_amount, a.updated_invoice_amount from master_table m, data_from_accounting a where m.job_number=a.job_number ) set m.invoice_amount=a.updated_invoice_amount; Note that the job number field in both tables in this example scenario are both primary keys.
We need to add two columns to the author table, Notice that the new columns are at the end of the AUTHOR table.