Home » SQL & PL/SQL » SQL & PL/SQL » Update Column with auto increment
Update Column with auto increment [message #330226] Sat, 28 June 2008 01:22 Go to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Dear Group members
I have two tables
Item and item_mast
[u]Item_cat[/u]
[u]itmcat [/u]                       [u]code[/u]
BEARINGS                             0201
BELTS                                0202
CAN                                  0104
LABORATORY ITEMS                     0203
MECHANICAL STORES                    0204
NUT BOLTS & NAILS                    0205
O-RINGS                              0206
CAN LOCAL                            0105
CAN EXPORT                           0106
CAPS                                 0107


And in item_mast table there is multiple items against each itmcat.
ITMCAT        DSCRIP                   UNT 	  CODE
------        ---------------------    ----      ------
BEARINGS     BEARING LOCK 50 MM        NOS
BEARINGS     BEARING NO.RNK-4906       NOS
BEARINGS     BEARING BODY NO.UC 205 T  NOS
BEARINGS     BEARING DOUBLE GOLI       NOS
BEARINGS     BALL BEARING NO.EE4TN     NOS
CAN          CAN LIDS (ALMINIUM) SOT   NOS
CAN          CAN EMPTY 330 ML TWIST    NOS
CAN          CAN LIDS (ALMINIUM) JL    NOS
CAN          CAN LIDS (ALMINIUM) RL    NOS


I want to update item_mast table on the basis of item table.
Suppose it takes the code for BEARINGS from ITEM table and update item_mast code column with new item desc number.
just like bearing code is 0201 and in item_mast table code column should be like 02010001.
See the below example you will guess.


ITMCAT        DSCRIP                   UNT 	  CODE
------        ---------------------    ----      ------
BEARINGS     BEARING LOCK 50 MM        NOS       02010001
BEARINGS     BEARING NO.RNK-4906       NOS       02010002
BEARINGS     BEARING BODY NO.UC 205 T  NOS       02010003
BEARINGS     BEARING DOUBLE GOLI       NOS       02010004 
BEARINGS     BALL BEARING NO.EE4TN     NOS       02010005
CAN          CAN LIDS (ALMINIUM) SOT   NOS       01040001
CAN          CAN EMPTY 330 ML TWIST    NOS       01040002
CAN          CAN LIDS (ALMINIUM) JL    NOS       01040003
CAN          CAN LIDS (ALMINIUM) RL    NOS       01040004


Please Tell me the update query to find the desired results.
Thanks

[Updated on: Sat, 28 June 2008 01:26]

Report message to a moderator

Re: Update Column with auto increment [message #330227 is a reply to message #330226] Sat, 28 June 2008 01:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guideline as stated in URL above
Re: Update Column with auto increment [message #330230 is a reply to message #330227] Sat, 28 June 2008 01:39 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

I am just modifying my message but you have sent me message reading guidelines. Know the edit button has not shown. I want to modify my message .
thanks.
Re: Update Column with auto increment [message #330232 is a reply to message #330226] Sat, 28 June 2008 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you know how to order your rows in item_mast table then you just have to join with item and use ROW_NUMBER function.
Of course only if your version supports it but we can't know as you didn't post it when it is requested in guidelines (with 4 decimals).

Regards
Michel
Re: Update Column with auto increment [message #330233 is a reply to message #330232] Sat, 28 June 2008 02:00 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

I am using oracle 9i.

I have using it upto 4 decimals. Because a ITMCAT "BEARINGS" may have more than 999 item DSCRIPtions. Please i want to get update query when ITMCAT changed then the code will start from one.

Thanks for reply
Re: Update Column with auto increment [message #330244 is a reply to message #330233] Sat, 28 June 2008 03:20 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Still waiting for reply.
Re: Update Column with auto increment [message #330250 is a reply to message #330244] Sat, 28 June 2008 03:57 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Michel has already given you a hint and the link to the function that will be likely to help you. Read through the link and see if you can come up with an attempt at coding it yourself. The more effort that you put in to helping yourself, the more effort others will put in when you become (un)stuck.

[Updated on: Sat, 28 June 2008 03:58]

Report message to a moderator

Re: Update Column with auto increment [message #330747 is a reply to message #330226] Tue, 01 July 2008 01:57 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thanks for help full link but i am still unable to solve my problem. I have used this query.

update store_mast set code = (select code||lpad(row_number()          
   over(partition by itmcat order by itmcat )+1,4,'0')   
   comp_code from item
   where store_mast.itmcat = item.itmcat)


Now the result is like this
ITMCAT        DSCRIP                   UNT 	  CODE
------        ---------------------    ----      ------
BEARINGS     BEARING LOCK 50 MM        NOS       02010001
BEARINGS     BEARING NO.RNK-4906       NOS       02010001
BEARINGS     BEARING BODY NO.UC 205 T  NOS       02010001
BEARINGS     BEARING DOUBLE GOLI       NOS       02010001
BEARINGS     BALL BEARING NO.EE4TN     NOS       02010001
CAN          CAN LIDS (ALMINIUM) SOT   NOS       01040001
CAN          CAN EMPTY 330 ML TWIST    NOS       01040001
CAN          CAN LIDS (ALMINIUM) JL    NOS       01040001
CAN          CAN LIDS (ALMINIUM) RL    NOS       01040001



but i need this result

ITMCAT        DSCRIP                   UNT 	  CODE
------        ---------------------    ----      ------
BEARINGS     BEARING LOCK 50 MM        NOS       02010001
BEARINGS     BEARING NO.RNK-4906       NOS       02010002
BEARINGS     BEARING BODY NO.UC 205 T  NOS       02010003
BEARINGS     BEARING DOUBLE GOLI       NOS       02010004
BEARINGS     BALL BEARING NO.EE4TN     NOS       02010005
CAN          CAN LIDS (ALMINIUM) SOT   NOS       01040001
CAN          CAN EMPTY 330 ML TWIST    NOS       01040002
CAN          CAN LIDS (ALMINIUM) JL    NOS       01040003
CAN          CAN LIDS (ALMINIUM) RL    NOS       01040004

I update the code column different for different categories but same code for one categories items. I want to give auto increment for all items within the same category. Please provide solution for this problem i will be very thank full to you.

[Updated on: Tue, 01 July 2008 02:07]

Report message to a moderator

Re: Update Column with auto increment [message #330757 is a reply to message #330747] Tue, 01 July 2008 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You misuse row_number function, especially "order by" clause is wrong.

In addition, in update you must have one level more but as you didn't post a test case, we can't show you.

Regards
Michel

[Updated on: Tue, 01 July 2008 02:32]

Report message to a moderator

Re: Update Column with auto increment [message #330769 is a reply to message #330757] Tue, 01 July 2008 03:23 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thanks for reply.
I have clearly mention the scenario in my previous messages. What do you mean by test case.
Once again i tell you the requirement.

I have two tables. 1- Item 2- store_mast "See 1st MSG"
Both are linked with itmcat column.
I have added one more column "code" in "store_mast".
In this column i want to update the store_mast table column "code" with "item" table column "code" suppose "0201" against "itmcat" "BEARINGS" and 4 digits concate with it like this 02010001. For more items it should be incremented automatically.

I have clearly defined these two table in my first message. only wrong with 1st message is that The name of the 1st table is "Item" instead of "itmcat".

Please tell me the update statement to accomplish this task. Because i have tried but unable to achieve this task.

Please tell me how can i clarify more than this.
Re: Update Column with auto increment [message #330771 is a reply to message #330769] Tue, 01 July 2008 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What do you mean by test case.

test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Update Column with auto increment [message #330778 is a reply to message #330771] Tue, 01 July 2008 03:49 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Ok once again i show you the scanario.
Two Tables 1- item 2-store_mast
                    Item
                   ------
itmcat                               code
BEARINGS                             0201
BELTS                                0202
CAN                                  0104
LABORATORY ITEMS                     0203
MECHANICAL STORES                    0204
NUT BOLTS & NAILS                    0205
O-RINGS                              0206
CAN LOCAL                            0105
CAN EXPORT                           0106
CAPS                                 0107


a few records taken.

2-store_mast adding code column varchar2(10) in it.
                         STORE_MAST
                         ----------
ITMCAT        DSCRIP                   UNT 	  CODE
------        ---------------------    ----      ------
BEARINGS     BEARING LOCK 50 MM        NOS
BEARINGS     BEARING NO.RNK-4906       NOS
BEARINGS     BEARING BODY NO.UC 205 T  NOS
BEARINGS     BEARING DOUBLE GOLI       NOS
BEARINGS     BALL BEARING NO.EE4TN     NOS
CAN          CAN LIDS (ALMINIUM) SOT   NOS
CAN          CAN EMPTY 330 ML TWIST    NOS
CAN          CAN LIDS (ALMINIUM) JL    NOS
CAN          CAN LIDS (ALMINIUM) RL    NOS




Here store_mast.itmcat = item.itmcat.
Code column should be like this.
require result

                         STORE_MAST
                         ----------
ITMCAT        DSCRIP                   UNT 	  CODE
------        ---------------------    ----      ------
BEARINGS     BEARING LOCK 50 MM        NOS       02010001
BEARINGS     BEARING NO.RNK-4906       NOS       02010002
BEARINGS     BEARING BODY NO.UC 205 T  NOS       02010003
BEARINGS     BEARING DOUBLE GOLI       NOS       02010004
BEARINGS     BALL BEARING NO.EE4TN     NOS       02010005
CAN          CAN LIDS (ALMINIUM) SOT   NOS       01040001
CAN          CAN EMPTY 330 ML TWIST    NOS       01040002
CAN          CAN LIDS (ALMINIUM) JL    NOS       01040003
CAN          CAN LIDS (ALMINIUM) RL    NOS       01040004



After using update query which i have made.
update store_mast set code = (select code||lpad(row_number()          
   over(partition by itmcat order by itmcat )+1,4,'0')   
   comp_code from item
   where store_mast.itmcat = item.itmcat)


Know the result is like this. Main focus on code column.
                         STORE_MAST
                         ----------
ITMCAT        DSCRIP                   UNT 	  CODE
------        ---------------------    ----      ------
BEARINGS     BEARING LOCK 50 MM        NOS       02010001
BEARINGS     BEARING NO.RNK-4906       NOS       02010001
BEARINGS     BEARING BODY NO.UC 205 T  NOS       02010001
BEARINGS     BEARING DOUBLE GOLI       NOS       02010001
BEARINGS     BALL BEARING NO.EE4TN     NOS       02010001
CAN          CAN LIDS (ALMINIUM) SOT   NOS       01040001
CAN          CAN EMPTY 330 ML TWIST    NOS       01040001
CAN          CAN LIDS (ALMINIUM) JL    NOS       01040001
CAN          CAN LIDS (ALMINIUM) RL    NOS       01040001



will you got the requirement.

Please provide me solution.
Re: Update Column with auto increment [message #330784 is a reply to message #330778] Tue, 01 July 2008 04:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem with your current code is that you run that subquery for each item in Store_Mast, and it only returns one row each time. Thus you never get a row_number bigger than one.

What you need to do is to select from a subquery that looks at all the rows for an ItmCat:

create table itm  (itmcat  varchar2(30), code varchar2(10));

insert into itm values ('BEARINGS','0201');
insert into itm values ('CAN','0104');

create table store_mast (ITMCAT varchar2(30),DSCRIP varchar2(30),UNT varchar2(3),CODE varchar2(10));

insert into store_mast values ('BEARINGS' ,    'BEARING LOCK 50 MM'       , 'NOS'   ,    '');
insert into store_mast values ('BEARINGS' ,    'BEARING NO.RNK-4906'      , 'NOS'   ,    '');
insert into store_mast values ('BEARINGS' ,    'BEARING BODY NO.UC 205 T' , 'NOS'   ,    '');
insert into store_mast values ('BEARINGS' ,    'BEARING DOUBLE GOLI'      , 'NOS'   ,    '');
insert into store_mast values ('BEARINGS' ,    'BALL BEARING NO.EE4TN'    , 'NOS'   ,    '');
insert into store_mast values ('CAN'      ,    'CAN LIDS (ALMINIUM) SOT'  , 'NOS'   ,    '');
insert into store_mast values ('CAN'      ,    'CAN EMPTY 330 ML TWIST'   , 'NOS'   ,    '');
insert into store_mast values ('CAN'      ,    'CAN LIDS (ALMINIUM) JL'   , 'NOS'   ,    '');
insert into store_mast values ('CAN'      ,    'CAN LIDS (ALMINIUM) RL'   , 'NOS'   ,    '');

update store_mast sm1
set code = (select code||to_char(rnum,'fm0000') 
            from   (select dscrip
                          ,itm.code
                          ,row_number() over (partition by itm.itmcat order by store_mast.dscrip) rnum
                    from   itm,store_mast
                    where  itm.ITMCAT = store_mast.itmcat) x
            where  x.dscrip = sm1.dscrip);
Re: Update Column with auto increment [message #330789 is a reply to message #330778] Tue, 01 July 2008 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I got your requirements, I understood your problem but you don't understand what is a test case even when I explain it.
Read JRowbottom's answer, he posted a test case; with what he posted I can copy and paste it in my database and start working.
Do you understand now what is a correct posting?

Regards
Michel
Re: Update Column with auto increment [message #330813 is a reply to message #330789] Tue, 01 July 2008 07:01 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thank Michel
I understood the test case know.I will keep in mind next time.
Re: Update Column with auto increment [message #330816 is a reply to message #330226] Tue, 01 July 2008 07:07 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Excellent query JRowbottom.
But still there is problem when i run this query it give error "Single row column return more then one row". This is due to duplicate values in "DSCRIP" Column in store_mast table. How we can assign code to duplicate values.

Re: Update Column with auto increment [message #330825 is a reply to message #330816] Tue, 01 July 2008 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not join on DSCRIP join on ROWID.

Regards
Michel
Re: Update Column with auto increment [message #330959 is a reply to message #330825] Tue, 01 July 2008 23:15 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thank Michel
It works quite well but it takes longer time to execute.
for 339 rows updated it has taken 5 to 6 mins. So It take a longer time to update 17000 rows. Is there any suggestion regarding time.

[Updated on: Tue, 01 July 2008 23:15]

Report message to a moderator

Re: Update Column with auto increment [message #330971 is a reply to message #330959] Tue, 01 July 2008 23:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus with "set autotrace on", execute the statement and post the result.

Regards
Michel
Re: Update Column with auto increment [message #331145 is a reply to message #330971] Wed, 02 July 2008 06:31 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

When i started this complete query it takes more than one and half hour and give this error after it.
Wrote file afiedt.buf

  1  update store_mast sm1
  2  set code = (select code||to_char(rnum,'fm0000') from
  3                     (select m.dscrip,s.code,row_number() over(partition by s.sub_cat_desc order by m.dscrip)rnum
  4                      from item_sub_cat s,store_mast m
  5                      where s.sub_cat_desc = m.itmcat)x
  6* where sm1.rowid = x.rowid)
  7  /
update store_mast sm1
       *
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 8
ORA-01628: max # extents (300) reached for rollback segment RB7


Please tell me why this error has occurred.
Re: Update Column with auto increment [message #331167 is a reply to message #331145] Wed, 02 July 2008 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-01628: max # extents (string) reached for rollback segment string
Cause: An attempt was made to extend a rollback segment that was already at the MAXEXTENTS value.
Action: If the value of the MAXEXTENTS storage parameter is less than the maximum allowed by the system, raise this value.


Your rollback segment is too small to contain all the modifications currently made and not committed by the transactions allocated to this rolback segment.

Regards
Michel
Re: Update Column with auto increment [message #331317 is a reply to message #331167] Thu, 03 July 2008 00:11 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

I have increase the rollback segment and run this query it has taken near about two hours.
SQL> update store_mast sm1 
  2  set code = (select code||to_char(rnum,'fm0000') from 
  3                     (select m.dscrip,s.code,row_number() over(partition by s.sub_cat_desc order 
by m.dscrip)rnum               
  4                      from item_sub_cat s,store_mast m
  5                      where s.sub_cat_desc = m.itmcat)x
  6  where sm1.rowid = x.rowid)
  7  /

15665 rows updated.


Statistics
----------------------------------------------------------
     135805  recursive calls
     131646  db block gets
  508648764  consistent gets
   30496784  physical reads
    7197940  redo size
        386  bytes sent via SQL*Net to client
        834  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
      15665  sorts (disk)
      15665  rows processed

Re: Update Column with auto increment [message #331328 is a reply to message #331317] Thu, 03 July 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add explain plan as well as table statistics, indexes information and so on.

Regards
Michel
Re: Update Column with auto increment [message #331335 is a reply to message #331328] Thu, 03 July 2008 01:11 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

According to you i have run this command before run this code but it give this error.

SQL> SET AUTOTRACE ON;
Unable to verify PLAN_TABLE format or existence
Error enabling EXPLAIN report


Why it did not enabled explain report.

Thanks & regards.

[Updated on: Thu, 03 July 2008 01:13]

Report message to a moderator

Re: Update Column with auto increment [message #331352 is a reply to message #331335] Thu, 03 July 2008 01:50 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask your DBA to install a plan table (execute utlxplan.sql).

Regards
Michel
Previous Topic: Data Dictionary
Next Topic: Retrieve one record for one id
Goto Forum:
  


Current Time: Fri Dec 09 17:47:25 CST 2016

Total time taken to generate the page: 0.21399 seconds