Home » SQL & PL/SQL » SQL & PL/SQL » update query (11.2.0.4)
update query [message #666482] |
Wed, 08 November 2017 06:15 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi,
I have one table and the data as below. Is there any way to update the id value with new values as below.
create table test (id VARCHAR2(10), name varchar2(10));
insert into test values(1,'A1');
insert into test values(2,'A2');
insert into test values(3,'A3');
insert into test values(4,'A4');
insert into test values(5,'A5');
insert into test values(6,'A6');
insert into test values(7,'A7');
insert into test values(8,'A8');
insert into test values(9,'A9');
insert into test values('E','A10');
insert into test values(1,'B1');
insert into test values('E','B10');
insert into test values('82-31081039-08','B10');
Current value(id) New value(id)
1 E
2 4
3 7
4 3
5 8
6 2
7 9
8 or 08 5
9 1
E 6
|
|
|
|
|
Re: update query [message #666503 is a reply to message #666486] |
Thu, 09 November 2017 04:23 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi,
I am using the following method to update.
1. First updating all the current values with new value by concatenating any character.
For example:
update test set id='EU' where id='1';
update test set id='E' where id='EU';
Please advice is there any better way.
|
|
|
Re: update query [message #666504 is a reply to message #666503] |
Thu, 09 November 2017 04:27 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The logic behind this is still as clear as mud.
You need to describe the actual logical rules you're trying to implement rather than just giving examples.
|
|
|
Re: update query [message #666506 is a reply to message #666503] |
Thu, 09 November 2017 06:22 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
grpatwari wrote on Thu, 09 November 2017 10:23Hi,
I am using the following method to update.
1. First updating all the current values with new value by concatenating any character.
For example:
update test set id='EU' where id='1';
update test set id='E' where id='EU';
Please advice is there any better way.
This surely is equivalent toupdate test set id='E' where id in ('1','EU'); Is that what you want?
|
|
|
|
Re: update query [message #666513 is a reply to message #666510] |
Thu, 09 November 2017 22:30 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Sorry, I think I have confused to all.
Actual requirement is as below.
create table test (id VARCHAR2(100), name varchar2(10));
insert into test values('82-31081019-E','A1');
insert into test values('82-31081022-E','A2');
insert into test values('82-31081026-03','A3');
insert into test values('82-31081034-04','A4');
insert into test values('82-31155877-05','A5');
insert into test values('82-31081035-08','A6');
insert into test values('82-31081022-07','A7');
insert into test values('82-31081013-06','A8');
insert into test values('82-31097518-09','A9');
insert into test values('82-31081026-02','A10');
insert into test values('82-31081044-01','B1');
insert into test values('82-31081030-01','B10');
insert into test values('82-31081039-08','B10');
Now I would like to update the value of id column after second underscore like
1 or 01 -> E , 02->04, 03->07, 04->03, 05->08, 06->02, 07->09, 08->05, 09->01, 0E or E ->06
for example: '82-31081039-08' value will be '82-31081039-05'
So need to update remaining values as well in the table at a time. I have taken two updates instead of one update by using regexp_replace function. Is there any better or simple way by performance wise.
|
|
|
Re: update query [message #666514 is a reply to message #666513] |
Thu, 09 November 2017 22:50 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
grpatwari wrote on Thu, 09 November 2017 20:30
Now I would like to update the value of id column after second underscore like
1 or 01 -> E , 02->04, 03->07, 04->03, 05->08, 06->02, 07->09, 08->05, 09->01, 0E or E ->06
03->07
07->09
09->01
01->E
E->06
06->02
02->04
04->03
go to top & start the same list again, again so where does it end?
|
|
|
Re: update query [message #666516 is a reply to message #666513] |
Fri, 10 November 2017 01:43 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> col id format a20
SQL> col new_id format a20
SQL> select id,
2 regexp_replace(id,
3 '-[^-]+$',
4 decode(regexp_substr(id, '-[^-]+$'),
5 '-1','-E', '-01','-E', '-02','-04',
6 '-03','-07', '-04','-03', '-05','-08',
7 '-06','-02', '-07','-09', '-08','-05',
8 '-09','-01', '-0E','-06', '-E','-06'))
9 new_id
10 from test
11 /
ID NEW_ID
-------------------- --------------------
82-31081019-E 82-31081019-06
82-31081022-E 82-31081022-06
82-31081026-03 82-31081026-07
82-31081034-04 82-31081034-03
82-31155877-05 82-31155877-08
82-31081035-08 82-31081035-05
82-31081022-07 82-31081022-09
82-31081013-06 82-31081013-02
82-31097518-09 82-31097518-01
82-31081026-02 82-31081026-04
82-31081044-01 82-31081044-E
82-31081030-01 82-31081030-E
82-31081039-08 82-31081039-05
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:35:55 CDT 2024
|