Oracle connection table update

Currently, the project database is changed from sqlserver to Oracle. Therefore, all SQL statements in the project must be changed to the oralce-supported style. This table is updated during the process. After an afternoon, I finally got it done.

Let's take a look at the original sentence:

Sqlserver: Update Table1 set field 1 = table2. Field 1, Field 2 = table2. Field 2, Field 3 = table2. Field 3 from (select field 4, Field 1, Field 2, field 3 from Table2 wheretime between' "+ time1 +" 'and' "+ time2 +" '"group by field 4) A where table2. field 4 = table1. field 4

 

Oracle syntax conversion is not supported

Then I tried to set fields one by one, but I couldn't update them in batches. Someone said

Update customers a -- use the alias set (city_name, customer_type) = (select B. city_name, B. customer_typefrom tmp_cust_city bwhere B. customer_id =. customer_id) where exists (select 1 from tmp_cust_city bwhere B. customer_id =. customer_id)

The reason why the attempt is not available is unknown.

Later I read a Post saying that Oracle only supports one record at set, so the following is the last successful sentence.

ORACLE: Update Table1 set (Field 1, Field 2, Field 3) = (select table2. Field 1, table2. Field 2, table2. Field 3 from (select field 4, Field 1, field 2, Field 3 from Table2 wheretime between '"+ time1 +"' and '"+ time2 +"' "group by field 4) A where table2. field 4 = table1. field 4)

In this case, the equal sign after the set statement has only one record, but the table at the innermost layer has several data records, so that batch update can be performed.

 

Method taken from: http://www.linuxidc.com/Linux/2011-01/31487.htm