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: 21229
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: 274
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: 12927
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: 7148
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: 4793
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: 25715
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: 65194
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: Order of Trigger Types
Next Topic: MATERIALIZED VIEW
Goto Forum:
  


Current Time: Sun Nov 19 15:34:18 CST 2017

Total time taken to generate the page: 0.01245 seconds