Set update case dynamic ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Set update case dynamic ?

Author

Topic

mberggren
Starting Member

15 Posts

Posted - 2009-05-11 : 05:33:56

Hi,

For now i´m doing an manual update of an column "categories" in a table like this (code below) and it works fine. But is there any way to make this more dynamic. Sometimes i don´t know actual number of categories so i have to make this manual case statement every time and declare new variables each time. So for example if there 200 hundred diffrent categories, there has to be a easier way to solve this.


DECLARE @counterA int
select @counterA=1000
DECLARE @counterB int
select @counterB=2000
 
UPDATE #custTable    
    
Set
    
@counterA = case
                
when cat = 'A' then @counterA + 1        
else num+@counterA
      
end
         

,@counterB = case
                 
when cat = 'B' then @counterB + 1                         
else num+@counterB            
     
end
         

,num = case
            
when cat = 'A' then num+@counterA                          
when cat = 'B' then num+@counterB                         
else num                 
       
end

Regards

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-11 : 08:48:06

try this one


create table #custTable(cat varchar(32),num int)
insert into #custTable select 'a',1
insert into #custTable select 'b',2

DECLARE @counterA int,@str varchar(max)
select @counterA=1000
DECLARE @counterB int
select @counterB=2000
DECLARE @counterc varchar
DECLARE @counterd varchar

 select @str ='',@counterc ='',@counterd=''
select @str ='UPDATE s    
Set num = val
from (select num,case
when cat = ''A'' then num +CAST('+CAST(@counterA+1  AS VARCHAR(32))  +' AS INT)                                                      
when cat = ''B'' then num+CAST('+CAST(@counterb+1  AS VARCHAR(32))  +' AS INT) else num+CAST('+CAST(@counterb  AS VARCHAR(32)) +' AS INT)                                          
end val from #custTable)s'

print (@str)
exec(@str)

select * from #custTable
drop table #custTable

mberggren
Starting Member

15 Posts

Posted - 2009-05-26 : 05:27:57

Now I have an excel sheet where you fill out item name, main cat and sub cat and then
I save it as an .csv file and the result of the .csv file i shown below.


High top 1, Shoes, High tops
High top 2, Shoes, High tops
High top 1, Shoes, High tops
Sandal 1, Shoes, Sandals
Sandal 2, Shoes, Sandals
Sandal 3, Shoes, Sandals
Boot 1, Shoes, Sandals
Boot 2, Shoes, Sandals
Boot 3, Shoes, Sandals
BS SHIRT 1, SHIRTS, BLACK SHIRTS
BS SHIRT 2, SHIRTS, BLACK SHIRTS
BS SHIRT 1, SHIRTS, BLACK SHIRTS
WS 1, SHIRTS, WHITE SHIRTS
WS 2, SHIRTS, WHITE SHIRTS
WS 3, SHIRTS, WHITE SHIRTS
BRS 1, SHIRTS, BROWN SHIRTS
BRS 2, SHIRTS, BROWN SHIRTS
BRS 3, SHIRTS, BROWN SHIRTS

After this i import it to my database with this statement:


CREATE TABLE custom.items (
mi_seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
obj_num varchar (16),
item_name (16),
main_cat CHAR (25),
sub_cat CHAR (25)
);
INPUT INTO custom.items FROM C:\items.csv format ASCII (mi_seq, obj_num, item_name, main_cat, sub_cat);

Result of this in the database:


1,,High top 1, Shoes, High tops
2,,High top 2, Shoes, High tops
3,,High top 1, Shoes, High tops
4,,Sandal 1, Shoes, Sandals
5,,Sandal 2, Shoes, Sandals
6,,Sandal 3, Shoes, Sandals
7,,Boot 1, Shoes, Sandals
8,,Boot 2, Shoes, Sandals
9,,Boot 3, Shoes, Sandals
10,,BS SHIRT 1, SHIRTS, BLACK SHIRTS
11,,BS SHIRT 2, SHIRTS, BLACK SHIRTS
12,,BS SHIRT 1, SHIRTS, BLACK SHIRTS
13,,WS 1, SHIRTS, WHITE SHIRTS
14,,WS 2, SHIRTS, WHITE SHIRTS
15,,WS 3, SHIRTS, WHITE SHIRTS
16,,BRS 1, SHIRTS, BROWN SHIRTS
17,,BRS 2, SHIRTS, BROWN SHIRTS
18,,BRS 3, SHIRTS, BROWN SHIRTS

Now, here is the part that is tricky. I want to use the field obj_num to catagories the database with the
main cat and sub cat as shown below. So every main cat starts with 10000 and
every sub cat under main cat starts with 100 as shows in the example. I could do all of this
manually with the code written in previous posts in this tread, but it would take for ever if I have like hundred
different sub cat

Example what I´m trying to accomplish: (These are just examples and don´t exits in the database
// Main Header // Main catagory and // Sub Header // Sub catagory)


1,10000,*** SHOES ***,, // Main Header // Main catagory
2,10100,**HIGH TOPS**,, // Sub Header // Sub catagory
3,10101,High top 1, Shoes, High tops
4,10102,High top 2, Shoes, High tops
5,10103,High top 1, Shoes, High tops
6,10200,**SANDALS**,, // Sub Header //
7,10201,Sandal 1, Shoes, Sandals
8,10202,Sandal 2, Shoes, Sandals
9,10203,Sandal 3, Shoes, Sandals
10,10300,**BOOTS**,, // Sub Header //
11,10301,Boot 1, Shoes, Sandals
12,10302,Boot 2, Shoes, Sandals
13,10303,Boot 3, Shoes, Sandals
14,20000,*** SHIRTS ***,, // Main Header // Main catagory
15,20100,**BLACK SHIRTS**,, // Sub Header // Sub catagory
16,20101,BS SHIRT 1, SHIRTS, BLACK SHIRTS
17,20102,BS SHIRT 2, SHIRTS, BLACK SHIRTS
18,20103,BS SHIRT 1, SHIRTS, BLACK SHIRTS
19,20200,**WHITE SHIRTS**,, // Sub Header //
20,20201,WS 1, SHIRTS, WHITE SHIRTS
21,20202,WS 2, SHIRTS, WHITE SHIRTS
22,20203,WS 3, SHIRTS, WHITE SHIRTS
23,20300,**BROWN SHIRTS**,,// Sub Header //
24,20301,BRS 1, SHIRTS, BROWN SHIRTS
25,20302,BRS 2, SHIRTS, BROWN SHIRTS
26,20303,BRS 3, SHIRTS, BROWN SHIRTS

// Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources