Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use a statement to implement update/insert statement". In daily operation, I believe many people have doubts about how to use a statement to implement update/insert statement. Xiaobian consulted various materials and sorted out simple and easy operation methods. I hope to help you answer the doubts of "how to use a statement to implement update/insert statement"! Next, please follow the small series to learn together!

Application scenario: When processing business data, update if the record exists in the database, and update if it does not exist.

Implementation SQL: (using oracle 9i versions later)

MERGE INTO [your table-name] [rename your table here]

USING ( [write your query here] )[rename your query-sql and using just like a table]

ON ([conditional expression here] AND [...]...)

WHEN MATHED THEN [here you can execute some update sql or something else ]

WHEN NOT MATHED THEN [execute something else here ! ]

Description:

using: The original data of update/insert is to update or insert the data found using these. That's why it's important to be able to track down the data.

on: judge condition, judge outer table record and using found record.

Merge: Merge is used to update the entire table. Use it carefully.

Examples of use:

merge into tfa_alarm_act_nms a

using (select FP0,FP1,FP2,FP3,REDEFINE_SEVERITY

from tfa_alarm_status) b

on (a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3)

when matched then update set a.redefine_severity=b.redefine_severity

when not matched then insert (a.fp0,a.fp1,a.fp2,a.fp3,a.org_severity,a.redefine_severity,

a.event_time ,a.int_id)

values (b.fp0,b.fp1,b.fp2,b.fp3,b.REDEFINE_SEVERITY,b.redefine_severity,sysdate,7777778);

Explanation: Use the table tfa_alarm_status to match b.redefine_servertify with the new label tfa_alarm_act_nms if a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3

Insert if data for this condition is not available in the tfa_alarm_act_nms table.

At this point, the study of "how to use a statement to implement update/insert statement" is over, hoping to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.