Shulou(Shulou.com)06/02 Report--

In this issue, Xiaobian will bring you about how to update and select associated operations in Oracle. The article is rich in content and analyzes and narrates from a professional perspective. After reading this article, I hope you can gain something.

1. Introduction

This article mainly introduces Oracle database update set select from association update, through the specific content to show you, I hope to help you learn Oracle database.

There is a requirement in the work. Now there are some data in the new table that are basically the same as those in the old table. In this way, you only need to move the data in the old table to the new table. At the same time, you can modify the data in different fields. When modifying the fields, you need to specify a condition, such as the primary key id, to modify a certain record. The efficiency of such a modification is too low. Is there a batch operation method?

Goods table:

select gid,code,name,code2 from goods;

init_goods table:

select code,name,code2 from init_goods;

2. Solution 2.1 Requirements

code2 in the goods table is updated according to code2 in the init_goods table;

2.2, error demonstration

Here's a mistake:

update goods set code2 = (select code2 from init_goods where init_goods.code = goods.code)

Goods table data cannot be updated, the system will always be stuck, know crash;

2.3 Solutions

Method 1:

update goods set code2 = (select code2 from init_goods where init_goods.code = goods.code)where exists (select 1 from init_goods where init_goods.code = goods.code)

Method 2:

update goods goodsset goods.code2 = (select init_goods.code2 from init_goods where init_goods.code = goods.code)where goods.code = (select init_goods.code from init_goods where init_goods.code = goods.code)

Note: The modified column is code2, and the condition is code ;

Description:

If the select clause can return multiple rows of records, but the records that fit the where condition are unique, otherwise a single-row error will be reported, because update can only match the corresponding records according to the where clause (inner where) here, and only one at a time.

The above is how to update and select in Oracle, if there is a similar doubt, you may wish to refer to the above analysis to understand. If you want to know more about it, please pay attention to the industry information channel.

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.