Home » SQL & PL/SQL » SQL & PL/SQL » update column for the set of records
update column for the set of records [message #682033] Tue, 29 September 2020 04:40 Go to next message
mist598
Messages: 1191
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 Go to previous messageGo to next message
John Watson
Messages: 8428
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 Go to previous messageGo to next message
mist598
Messages: 1191
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 #682037 is a reply to message #682036] Tue, 29 September 2020 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67560
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

John Watson wrote on Fri, 05 October 2018 12:44
...
What SQL have you tried so far?
Re: update column for the set of records [message #682038 is a reply to message #682037] Tue, 29 September 2020 06:37 Go to previous messageGo to next message
mist598
Messages: 1191
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....
Re: update column for the set of records [message #682039 is a reply to message #682038] Tue, 29 September 2020 07:12 Go to previous message
Michel Cadot
Messages: 67560
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 29 September 2020 13:03

John Watson wrote on Fri, 05 October 2018 12:44
...
What SQL have you tried so far?
Previous Topic: find the next occurrence of an event based on another event
Next Topic: Group pivot column
Goto Forum:
  


Current Time: Wed Dec 02 19:55:23 CST 2020