December 31, 2010 at 1:41 am
Hi,
I am having two tables. like below.
Table1:
Sequence_ididNameKeyitem_namerule_id
1 6761Includes0NULL
2 6761item160abc
3 6761item163xyz
4 6761Requires0NULL
5 6761item6760abc
6 6761Only For0NULL
7 6761Item271abc
8 6761Item1884xyz
Table2:
rule_id Name
1 (
2 )
10 Includes
11 Mandatory Deletes
14 Only For
15 Or
16 Requires
Now :
rule_id in table1 needs to be updated with rule_id from table2 by matching the table1-name column and table2-name column.when I update the result as given below.
Table1
id Name Key item_name rule_id
6761 Includes 0 NULL 10
6761 item 160 abc NULL
6761 item 163 xyz NULL
6761 Requires 0 NULL 16
6761 item 6760 abc NULL
6761 Only For 0 NULL 14
6761 Item 271 abc NULL
6761 Item 1884 xyz NULL
But as per the requirement , in rule_id the value 10 shloud be inserted 3 times,16 should be inserted two times,and 14 should be inserted 2 times. like below.. Pls help me to update the rule_id column...
OUTPUT Table1:
id Name Key item_name rule_id
6761 Includes 0 NULL 10
6761 item 160 abc 10
6761 item 163 xyz 10
6761 Requires 0 NULL 16
6761 item 6760 abc 16
6761 Only For 0 NULL 14
6761 Item 271 abc 14
6761 Item 1884 xyz 14
Thanks...
December 31, 2010 at 3:53 am
Hi
declare @t table (sequence_id int,id int,name_key varchar(10),item_name int,rule_id varchar(10))
insert into @t values (1,6761,'Includes',0, NULL )
insert into @t values (2,6761,'item',160,'abc')
insert into @t values (3,6761,'item',163,'xyz')
insert into @t values (4,6761,'Requires',0,NULL)
insert into @t values (5,6761,'item',6760,'abc')
insert into @t values (6,6761,'Only For',0,NULL)
insert into @t values (7,6761,'Item',271,'abc')
insert into @t values (8,6761,'Item',1884,'xyz')
declare @t1 table (rule_id varchar(10),name varchar(20))
insert into @t1 values (10,'Includes')
insert into @t1 values (11,'Mandatory Deletes')
insert into @t1 values (14,'Only For')
insert into @t1 values (15,'Or')
insert into @t1 values (16,'Requires')
declare @t2 table(sequence_id int,id int,name_key varchar(10),item_name int,
rule_id varchar(10),rule_id1 varchar(10))
insert into @t2
select a.*,isnull(b.rule_id,'-1') from @t a left outer join @t1 b on b.name = a.name_key
declare @v_name varchar(10),@v_ruleid varchar(10),@v_itemname varchar(10),
@v_value varchar(10),@v_value1 varchar(10)
declare @t3 table(sequence_id int,id int,name_key varchar(10),item_name int,
rule_id varchar(10),rule_id1 varchar(10))
declare c1 cursor for select name_key,item_name,rule_id1 from @t2
open c1
fetch c1 into @v_name,@v_itemname,@v_ruleid
while (@@FETCH_STATUS = 0)
begin
select @v_value = rule_id1 from @t2 where name_key = @v_name and rule_id1 = @v_ruleid
if @v_value > 0
begin
select @v_value1 = @v_value
end
insert into @t3 select sequence_id,id,name_key,item_name,rule_id,@v_value1
from @t2 where name_key = @v_name and item_name = @v_itemname
fetch next from c1 into @v_name,@v_itemname,@v_ruleid
end
close c1
deallocate c1
select * from @t3
Siva Kumar J
December 31, 2010 at 3:53 am
This should help you out
DECLARE @tbl_Table1 TABLE
(
Sequence_id INT,
idINT,
NameVARCHAR(100),
[Key]INT,
item_nameVARCHAR(100),
rule_idINT
)
DECLARE@tbl_Table2 TABLE
(
rule_id INT,
Name VARCHAR(100)
)
INSERT INTO @tbl_Table1 VALUES( 1, 6761, 'Includes', 0, NULL, NULL )
INSERT INTO @tbl_Table1 VALUES( 2, 6761, 'item', 160, 'abc', NULL )
INSERT INTO @tbl_Table1 VALUES( 3, 6761, 'item', 163, 'xyz', NULL )
INSERT INTO @tbl_Table1 VALUES( 4, 6761, 'Requires', 0, NULL, NULL )
INSERT INTO @tbl_Table1 VALUES( 5, 6761, 'item', 6760, 'abc', NULL )
INSERT INTO @tbl_Table1 VALUES( 6, 6761, 'Only For', 0, NULL, NULL )
INSERT INTO @tbl_Table1 VALUES( 7, 6761, 'Item', 271, 'abc', NULL )
INSERT INTO @tbl_Table1 VALUES( 8, 6761, 'Item', 1884, 'xyz', NULL )
INSERT INTO @tbl_Table2 VALUES( 1, '(' )
INSERT INTO @tbl_Table2 VALUES( 2, ')' )
INSERT INTO @tbl_Table2 VALUES( 10, 'Includes' )
INSERT INTO @tbl_Table2 VALUES( 11, 'Mandatory Deletes' )
INSERT INTO @tbl_Table2 VALUES( 14, 'Only For' )
INSERT INTO @tbl_Table2 VALUES( 15, 'Or' )
INSERT INTO @tbl_Table2 VALUES( 16, 'Requires' )
UPDATET1
SETrule_id = T2.rule_id
FROM@tbl_Table1 T1
INNER JOIN @tbl_Table2 T2 ON T1.Name = T2.Name
UPDATET1
SETT1.rule_id = T2.rule_id
FROM@tbl_Table1 T1
CROSS APPLY(
SELECTTOP 1 rule_id
FROM@tbl_Table1 T2
WHERET1.Sequence_id > T2.Sequence_id
ANDT2.rule_id IS NOT NULL
ORDER BY T2.Sequence_id DESC
) T2
WHERET1.rule_id IS NULL
SELECT * FROM @tbl_Table1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply