Update fields for another table in Oracle with one table field

In the process of doing the project today, it is found that a field in a table in the development library has many values that are empty, while the value of the field in the test library is there.

So, what is the way to update the value of the field in the test library to the development library?

This is easier to solve in SQL Server, and the method is not known in Oracle.

Workarounds for similar issues in SQL Server

Later had to use the most stupid method:

First, copy the data to Excel; (assuming that the table for the test library is a--contains data)

Then, in the development library, build table B with the same structure as table A; (here for the simple import of data, I have modified the structure of table B, only two fields)

Data for Chart b

The import function of PL SQL is used to import this data into table B (at this time table B's data is a subset of table a);

The next thing to do is to update table B's data to the corresponding table in the development library, assuming it's called Table D;

The merge into in Oracle is used here.

SQL code is as follows:

 into dusing B  on = /* And B is NULL */ When then ,  UPDATESET=  B . Relavance_propety

A detailed explanation of merge into

However, an error occurred during this process:

Error 1:

A ORA-30926 error occurred while performing the merge into operation.

What is the cause of the error? How to solve it?

Reason:

Baidu a bit, generally know because table B contains duplicate key, here the key is the condition of the category_name, from the condition:

D.category_name = B.category_name

can be seen.

Add:

　　

Solve:

Knowing the reason above, all we have to do is delete the records with duplicate category_name.

The following SQL is used to obtain which category_name values are duplicated:

SELECT Category_name,COUNT(1 from BGROUP by category_name  having COUNT (1>1

The effect is as follows:

The next step is to delete the duplicated data and execute the following statement into edit mode:

SELECT *  from B MM WHERE inch (SELECT from BGROUP by category_name  haveCOUNT(1>1forUPDATE

The effect is as follows:

Then select the data you want to delete.

The table on our side has only 2 fields, so we can use the group by result to dump to the temporary table, and then use the temporary table to overwrite the original table method to wash the data.

But more cases are: (1) More than two fields, and (2) a record of the same field, other fields may be different (i.e. not identical)

Error 2:

When you make a backup of table B, you want to copy the entire table to a new table, which is often used:

SELECT * Into new_table from old_table

To do such a thing. The expected result is that the data in the table is copied to the new_table at the same time as the table structure is replicated.

As a result, the following error occurred:

Why is it?

Reason:

The original select INTO is a PL/SQL assignment Statement! The format used for this is inconsistent with the assignment format.

Therefore, the ORA-00905 error will be reported.

Solve:

So how do you solve a similar problem in PL/SQL?

That is, create table with the following statement:

-- duplicating table structures and data CREATE TABLE  as SELECT *  from B

As followed by a query statement.

Update fields for another table in Oracle with one table field