Update associated update Implementation in sqlserver and Oracle

Source: Internet
Author: User

The syntax for implementing update Association update in SQL Server and Oracle is different. You can use inline view (embedded view)
In general, sqlserver is simpler. The test example is as follows:

Create Table tmp_a
(Cpcode varchar2 (10 ),
Sb_ym varchar2 (6 ),
Flag char (1)
);

Create Table tmp_ B
(Cpcode varchar2 (10 ),
Sb_ym varchar2 (6 ),
Flag char (1)
);

Insert into tmp_a (cpcode, sb_ym, flag) values ('20140901', '20160901', 'E ');
Insert into tmp_a (cpcode, sb_ym, flag) values ('20140901', '20160901', 'E ');
Insert into tmp_ B (cpcode, sb_ym, flag) values ('123456', '123456', 'R ');
Insert into tmp_ B (cpcode, sb_ym, flag) values ('123456', '123456', 'R ');
Insert into tmp_ B (cpcode, sb_ym, flag) values ('123456', '123456', 'R ');
Insert into tmp_ B (cpcode, sb_ym, flag) values ('20140901', '20160901', 'E ');
Commit;

In sqlserver:

Update tmp_ B set flag = B. flang from tmp_a A, tmp_ B B
Where a. cpcode = B. cpcode and A. sb_ym = B. sb_ym;

In ORACLE:

Method 1: (low efficiency)
Update tmp_ B
Set flag = (select flag from tmp_a B
Where a. cpcode = B. cpcode and A. sb_ym = B. sb_ym)
Where exists
(Select * From tmp_a C
Where a. cpcode = C. cpcode and A. sb_ym = C. sb_ym );

Statistics
----------------------------------------------------------
8 recursive cballs
3 dB block gets
18 consistent gets
0 physical reads
0 redo size

Method 2: (high efficiency)
Alter table tmp_a add constraint p_tmp_a primary key (cpcode, sb_ym );

Update (select B. Flag flagb, A. Flag flaga
From tmp_a A, tmp_ B B
Where a. cpcode = B. cpcode
And a. sb_ym = B. sb_ym)
Set flagb = flaga;

Statistics
----------------------------------------------------------
0 recursive cballs
3 dB block gets
7 consistent gets
0 physical reads
0 redo size

Note: The primary key must be added to the data source table in method 2; otherwise, an error is returned.
ORA-01779: The columns corresponding to the non-key save table cannot be modified
You do not need to add a primary key to the modified table.

 

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.