Home » SQL & PL/SQL » SQL & PL/SQL » update query (11.2.0.4)
update query [message #666482] Wed, 08 November 2017 06:15 Go to next message
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 #666483 is a reply to message #666482] Wed, 08 November 2017 06:30 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Current value: 1
New value: E

Why?
Re: update query [message #666486 is a reply to message #666483] Wed, 08 November 2017 07:48 Go to previous messageGo to next message
Alien
Messages: 289
Registered: June 1999
Senior Member
I can't figure out the logic, so I would just use a decode.

Regards,

Arian
Re: update query [message #666503 is a reply to message #666486] Thu, 09 November 2017 04:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
grpatwari wrote on Thu, 09 November 2017 10:23
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.
This surely is equivalent to
update test set id='E' where id in ('1','EU');
Is that what you want?
Re: update query [message #666510 is a reply to message #666503] Thu, 09 November 2017 08:13 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
You've been here 10 years and you post garbage like this?
Re: update query [message #666513 is a reply to message #666510] Thu, 09 November 2017 22:30 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68625
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
Previous Topic: MATERIALIZED VIEW
Next Topic: oracle external table issue
Goto Forum:
  


Current Time: Fri Mar 29 06:07:49 CDT 2024