Updating multiple columns in oracle

26-Sep-2019 14:27

In this scenario, accounting only sent me two records, but in the real life situation I ran into, it was over 10,000 records.Also, this scenario's master table has only 5 records; my real master table has close to 1,000,000 records. You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions.This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. By joining today you can post your own programming questions, respond to other developers questions, and eliminate the ads that are displayed to guests. Hii Alll Is it possible to update multiple column based on case statement??SQL courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

updating multiple columns in oracle-71updating multiple columns in oracle-6

To remedy that, we could throw in a where clause to the update statement, as below: -- Better example, but still inefficient update master_table m set m.invoice_amount=( select a.updated_invoice_amount from data_from_accounting a where m.job_number=a.job_number ) where m.job_number in( select a2.job_number from data_from_accounting a2 where m.job_number=a2.job_number ); This would correctly update the records I wanted to update while leaving the others alone, but this query is a little inefficient as it needs to go through the data from accounting twice.

In Oracle, when you need to update values of certain records from one table, and the value and the records to update are determined by a second table, you can use a subquery in the update statement to achieve the updating efficiently.

I ran into a situation today when I need to update 10,000 records in a master table totaling 1,000,000 rows, and the value that I'm going to use comes from the accounting department. In sum, accounting wants to update job 103's invoice amount to 0 and job 104's invoice amount to 0, while leaving all other amounts the same.

UPDATE table SET (col1,col2) =3D (SELECT col3,col4 FROM table2 WHERE table1=3Dtable2.id) Martin -----Urspr=FCngliche Nachricht-----" id="ctl00_m_m_i_ctl00_gr_ctl03_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="525332" // from empupd a , emp b * ERROR at line 4: ORA-00933: SQL command not properly ended Elapsed: .00 Thanks Regards Ajay K. Centre Dell International Services Bangalore, India Austin Dial-in : 753-78897 Phone : (080) 28077665 Cell : (0) 9886623796" id="ctl00_m_m_i_ctl00_gr_ctl05_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="525334" // from empupd a , emp b * ERROR at line 4: ORA-00933: SQL command not properly ended Elapsed: .00 Thanks Regards Ajay K.

If you are an application programmer then you will more than likely need to write TSQL code to update your SQL Server database tables.

To remedy that, we could throw in a where clause to the update statement, as below: -- Better example, but still inefficient update master_table m set m.invoice_amount=( select a.updated_invoice_amount from data_from_accounting a where m.job_number=a.job_number ) where m.job_number in( select a2.job_number from data_from_accounting a2 where m.job_number=a2.job_number ); This would correctly update the records I wanted to update while leaving the others alone, but this query is a little inefficient as it needs to go through the data from accounting twice.In Oracle, when you need to update values of certain records from one table, and the value and the records to update are determined by a second table, you can use a subquery in the update statement to achieve the updating efficiently.I ran into a situation today when I need to update 10,000 records in a master table totaling 1,000,000 rows, and the value that I'm going to use comes from the accounting department. In sum, accounting wants to update job 103's invoice amount to 0 and job 104's invoice amount to 0, while leaving all other amounts the same.UPDATE table SET (col1,col2) =3D (SELECT col3,col4 FROM table2 WHERE table1=3Dtable2.id) Martin -----Urspr=FCngliche Nachricht-----" id="ctl00_m_m_i_ctl00_gr_ctl03_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="525332" // from empupd a , emp b * ERROR at line 4: ORA-00933: SQL command not properly ended Elapsed: .00 Thanks Regards Ajay K. Centre Dell International Services Bangalore, India Austin Dial-in : 753-78897 Phone : (080) 28077665 Cell : (0) 9886623796" id="ctl00_m_m_i_ctl00_gr_ctl05_bestanswerbody" class="textarea-bestanswerhidden" name="bestanswerbody" answerbody Id="525334" // from empupd a , emp b * ERROR at line 4: ORA-00933: SQL command not properly ended Elapsed: .00 Thanks Regards Ajay K. If you are an application programmer then you will more than likely need to write TSQL code to update your SQL Server database tables.UPDATE The Oracle UPDATE statement locates one or more rows (or all the rows) in a table and sets one or more columns to the specified values.