update column for the set of records [message #682033] |
Tue, 29 September 2020 04:40 |
|
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I need to insert the seq_num and update order_id automatically for set of seq_num values.
update order_id as 10 for 1 to 3 seq_num
update order_id as 20 for 4 to 6 seq_num so on...
select 1 seq_num, 10 order_id from dual
union all
select 3 seq_num, 10 order_id from dual
union all
select 3 seq_num, 10 order_id from dual
union all
select 4 seq_num, 20 order_id from dual
union all
select 5 seq_num, 20 order_id from dual
union all
select 6 seq_num, 20 order_id from dual
Thank you
|
|
|
Re: update column for the set of records [message #682034 is a reply to message #682033] |
Tue, 29 September 2020 04:49 |
John Watson
Messages: 8939 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
For example,orclz> select rownum,round((rownum+1)/3)*10 from dual connect by level < 10;
ROWNUM ROUND((ROWNUM+1)/3)*10
---------- ----------------------
1 10
2 10
3 10
4 20
5 20
6 20
7 30
8 30
9 30
9 rows selected.
orclz>
|
|
|
Re: update column for the set of records [message #682036 is a reply to message #682034] |
Tue, 29 September 2020 05:30 |
|
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Thanks for your prompt response John.
Sorry I missed few points to provide here and providing you my original code.
select 'ABC' order_num, 'ABC-1' order_name from dual
union all
select 'ABC100' order_num, 'ABC-2' order_name from dual
union all
select 'ABC102' order_num, 'ABC-3' order_name from dual
union all
select 'ABC103' order_num, 'ABC-4' order_name from dual
union all
select 'ABC104' order_num, 'ABC-5' order_name from dual
union all
select 'ABC105' order_num, 'ABC-6' order_name from dual
union all
select 'ABC106' order_num, 'ABC-7' order_name from dual
union all
select 'ABC107' order_num, 'ABC-8' order_name from dual
union all
select 'ABC108' order_num, 'ABC-9' order_name from dual
I have data in the table like above and need to insert sequence , order_id automatically for set of seq_num values.
NOTE: 1 to 10000 --10 order_id,10001 to 20000 ---20 etc...
Thanks again
[Updated on: Tue, 29 September 2020 05:36] Report message to a moderator
|
|
|
|
Re: update column for the set of records [message #682038 is a reply to message #682037] |
Tue, 29 September 2020 06:37 |
|
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
select 'ABC' order_num, 'ABC-1' order_name from dual
union all
select 'ABC100' order_num, 'ABC-2' order_name from dual
union all
select 'ABC102' order_num, 'ABC-3' order_name from dual
union all
select 'ABC103' order_num, 'ABC-4' order_name from dual
union all
select 'ABC104' order_num, 'ABC-5' order_name from dual
union all
select 'ABC105' order_num, 'ABC-6' order_name from dual
union all
select 'ABC106' order_num, 'ABC-7' order_name from dual
union all
select 'ABC107' order_num, 'ABC-8' order_name from dual
union all
select 'ABC108' order_num, 'ABC-9' order_name from dual
The above one, i need to inesrt 10 order_id for 1 to 10000 ,20 for 10001 to 20000 etc....
|
|
|
|