Update Column with auto increment [message #330226] |
Sat, 28 June 2008 01:22  |
sweetkhaliq
Messages: 200 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 #330250 is a reply to message #330244] |
Sat, 28 June 2008 03:57   |
pablolee
Messages: 2882 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   |
sweetkhaliq
Messages: 200 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 #330784 is a reply to message #330778] |
Tue, 01 July 2008 04:23   |
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);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|