Use Cases of Oracle update + with, oracleupdate

Source: Internet
Author: User

Use Cases of Oracle update + with, oracleupdate
Drop table test purge;
Create table test (
Id number,
Code varchar (20 ),
Name varchar (20)
);
Insert into test values (1, '20170101', 'aaa ');
Insert into test values (2, '20170101', 'bbb ');
Insert into test values (3, '20170101', 'ccc ');
Insert into test values (4, '20170101', 'ddd ');
Insert into test values (5, '20170101', 'eee ');
Insert into test values (6, '20170101', 'fff ');
Commit;

-- Now we have this requirement. If the code already exists, add 1, 2, 3 to the group based on the code,
-- For example, for a record with code = 201402, the code is: 201402_1 and 201402_2.
-- 1. You can use the analysis function to spell out the code.

SQL> select t. id, code | '_' | row_number () over (partition by code order by id) cc from test t;
ID CC
-----------------------------------------------------------------------
1 201401_1
2 201402_1
3 201402_2
4 201403_1
5 201403_2
6 201403_3
You have selected 6 rows.
-- 2. I can't see it in traditional writing, but I can't find it.
SQL> update test t set t. code = (select code | '_' | row_number ()
Over (partition by code order by id) code
From test t1 where t1.id = t. id );
Six rows have been updated.
SQL> select * from test;
ID CODE NAME
--------------------------------------------------
1 201401_1 aaa
2 201402_1 bbb
3 ccc 402_1 ccc
4 201403_1 ddd
5 201403_1 eee
6 201403_1 fff
You have selected 6 rows.
SQL> rollback;

-- It seems that you need to create a temporary table and use update and merge, but there is another way to write


-- 3. Combination of update and
SQL> update test B set B. code = (
With t
(Select t. id, code | '_' | row_number () over (partition by code order by id) code
From test t)
Select a. code from t a where a. ID = B. ID
);
Six rows have been updated.

SQL> select * from test;
ID CODE NAME
--------------------------------------------------
1 201401_1 aaa
2 201402_1 bbb
3 ccc 402_2 ccc
4 201403_1 ddd
5 201403_2 eee
6 201403_3 fff


You have selected 6 rows.

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.