Oracle Bulk Update table fields

Oracle Bulk Update table field createtime--2018 February 27 16:02:24author:marydon

(i) Replace the numbers with Chinese characters

The first step is to re-query

Use the DISTINCT keyword to check the value of the field first, to see the total number of cases

--querying the value of a table in a specified interval fieldSELECT DISTINCTT.close_tz fromConsult_schedule TWHERET.schedule_datebetweenTo_date ('2018-01-01','YYYY-MM-DD') andTo_date ('2018-02-28','YYYY-MM-DD');

Query results: There are 3 kinds of cases

　　

The second step, distinguish the different values and update the corresponding Chinese characters

0 change to Yes, 1 to No

Use the Decode function to implement

--Correct SQLUPDATEConsult_schedule TSETT.replace_tz=DECODE (Replace_tz,'1','No','0','is a') WHERET.replace_tzinch('0','1')    andT.schedule_datebetweenTo_date ('2018-01-01','YYYY-MM-DD') andTo_date ('2018-02-28','YYYY-MM-DD');

Easy Error Point Description:

--error Sql1:close_tz field is VARCHAR2 typeUPDATEConsult_schedule TSETT.close_tz=DECODE (Close_tz,1,'No',0,'is a') WHERET.schedule_datebetweenTo_date ('2018-01-01','YYYY-MM-DD') andsysdate; 　　 

　　

Parse:close_tz field is VARCHAR2 type, 1 and 0 are number types

--error SQL2: Using the Decode () function, it will not be empty in the specified caseUPDATEConsult_schedule TSETT.close_tz=DECODE (Close_tz,'1','No','0','is a') WHERET.schedule_datebetweenTo_date ('2018-01-01','YYYY-MM-DD') andsysdate;

After the update, execute the above query statement again

　　

Cause: Depending on the results of the first query execution, you can draw the correct results using the Yes and No 2 cases, but this is 3 cases

Parsing: This is due to improper use of the Decode function and should be removed from columns that do not need to be changed.

Tip: When you modify database data, after executing the UPDATE statement, do not rush to commit, execute a query to see if it is the results you want, and then submit.

(ii) Give the value of one field to another field

UPDATE  SET T.id_card=WHEREisNULL;

(c) Bulk modification of specified characters in table fields

updatetime--2017 August 23 08:44:34

1.1.22 the specified string in the Replace field

-- converts the # number in the field image_adress in the Patient_auto_imageinfo table to _ UPDATE Patient_auto_imageinfo    SET = REPLACE ' # ' ' _ '  WHERElike'%#%'

(iv) batch modification by ID

UPDATE Tsorgdiaitem     Set = = = sysdate WHERE inch (1563287515612443)

Oracle Bulk Update table fields