Oracle Bulk Update table fields

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.