Oracle Updates another table field with one table field three ways ____oracle

1. Update (SELECT ..... ) Set column1 = Column2;

Update (select Iy.company_name company_name1, CC.COMPANY_NAME_JC company_name2
From Income_year_item iy, City_company cc
where Iy.company_code = Cc.code
)
Set company_name1 = Company_name2;

2. Only single-line subquery s

Update City_project_scale_info C Set
(c.value) = (
Select D.value from City_project_scale_info @test d where d.project_id = ' 7d7fd580a06240b2a9137dc2bbe831e9 '
and d.project_id = c.project_id and C.company_code = D.company_code
)
where exists (
Select 1 from City_project_scale_info @test d where d.project_id = ' 7d7fd580a06240b2a9137dc2bbe831e9 '
and d.project_id = c.project_id and C.company_code = D.company_code
)

If you pay particular attention to the WHERE clause here, you can try to do the following sentence without writing a WHERE clause, and you will make many of the values in the City_project_scale_info empty.

This is because if the WHERE clause is not written in an Oracle UPDATE statement, Oracle will update all of the values by default, even if you use subqueries here and some values are not found in subqueries, you assume that Oracle might skip these values, You are wrong, Oracle will update the value of the row to null.

3. Use Merg inot statement

--Update Production base fields
Merge into City_cfg_data_column_common CF1 using
City_cfg_data_column_common2 CF2 on
(Cf1.resourceid = Cf2.resourceid)
When matched then
Update Set cf1.template_type = Cf2.template_type,
Cf1.chinese_name = Cf2.chinese_name,
Cf1.column_name = Cf2.column_name,
Cf1.column_type = Cf2.column_type,
Cf1.column_size = Cf2.column_size
When not matched then
Insert (cf1.resourceid,cf1.template_type,cf1.chinese_name,cf1.column_name,cf1.column_type,cf1.column_size
, Cf1.is_can_edit,cf1.is_unique,cf1.is_can_cover,cf1.show_order)
VALUES (cf2.resourceid,cf2.template_type,cf2.chinese_name,cf2.column_name,cf2.column_type,cf2.column_size
, Cf2.is_can_edit,cf2.is_unique,cf2.is_can_cover,cf2.show_order)