HOW 2 UPDATE DATA INCREMENTALLY in SP ?? PLEASE!!!
-
Hi ALL,
SOMEBODY PLEASE HELP ME....
i have 2 table,
first table is CUSTOMER table that have 2 field (id_cust,id_support)
second table is SUPPORT table that have 3 field (id_support, support_name,role)
CUSTOMER TABLE
id_cust id_support
3301 0
3302 0
3303 0
3304 0
3305 0
3306 0
SUPPORT TABLE
id_support support_name role
011 john 2
012 smith 3
013 rina 4
i wanna insert data from SUPPORT TABLE (id_support) to CUSTOMER TABLE (id_support) incrementally based on sum of id_support
and i wanna use a MS SQL SERVER STORED PROCEDURE to execute that..
n the result that i want is :
CUSTOMER TABLE
id_cust id_support
3301 011
3302 012
3303 013
3304 011
3305 012
3306 013
THANX BEFORE...
REGARD ZAC
-
HI ALL,
i just try to solve my problem..but it can't run..
this my code trial :
CREATE PROCEDURE SP_TRY
@ID_DATEL numeric
AS
DECLARE
@ID_PELANGGAN AS NUMERIC,
@ID_SUPPORT AS VARCHAR(20),
@i AS INT,
@j-2 AS INT
/*DECLARE csr1 CURSOR FOR
select id_support from rc_support_3bln where id_datel=2
*/
DECLARE csr1 CURSOR FOR
select * from rc_sortir_intag
SET @i = 1
SET @j-2 = 1
/*
OPEN csr1
FETCH NEXT FROM csr1
INTO @ID_PELANGGAN, @ID_SUPPORT
*/
/*OPEN csr2
FETCH NEXT FROM csr2
*/
WHILE @i < 3
BEGIN
WHILE @j-2 < 4
BEGIN
OPEN csr1
FETCH NEXT FROM csr1
INTO @ID_PELANGGAN, @ID_SUPPORT
update rc_sortir_intag set id_support=@j
/*FETCH NEXT FROM csr2
*/
FETCH NEXT FROM csr1
CLOSE csr1
DEALLOCATE csr1
END
SET @j-2 =1
SET @i = @i + 1
END
/*
CLOSE csr1
DEALLOCATE csr1
*/
is there somebody can help me??
-
May I emphasize BP id_columns should not be used but for PK/FK perposes, so a count(*) for id_columns should only determine the number of usage, but nothing else ?!
Can you provide a correct simple select query to select the result you want ? (you may want to split it up into 2 or 3 parts)
Avoid using a cursor !
What's the relationship with rc_sortir_intag ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
thanx before..
the problem i faced is query for distribute 3 id_support to handle amount of id_cust where thats distribute incremental based on id_support...
the source and the result will like that (first posting)
What's the relationship with rc_sortir_intag ?
rc_sortir_intag is source table for id_cust...
-
is this what you are looking for ?
update Customer
set id_support = S.id_support
from Customer C
inner join (
Select id_cust, count(*) as counter
from rc_sortir_intag
group by id_cust) CC
on C.id_cust = CC.id_cust
inner join Support S
on CC.couter = S.role
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
so you could get this :
create proc spc_xxx
as
begin
set nocount on
-- first table is CUSTOMER table that have 2 field (id_cust,id_support)
-- second table is SUPPORT table that have 3 field (id_support, support_name,role)
-- the problem i faced is query for distribute 3 id_support to handle amount of id_cust where thats distribute incremental based on id_support...
-- the source and the result will like that (first posting)
-- What's the relationship with rc_sortir_intag ?
-- rc_sortir_intag is source table for id_cust...
-- update existing
update Customer
set id_support = S.id_support
from Customer C
inner join (
Select id_cust, count(*) as counter
from rc_sortir_intag
group by id_cust) CC
on C.id_cust = CC.id_cust
inner join Support S
on CC.couter = S.role
-- insert new
insert into Customer (id_cust, id_support)
Select CC.id_cust, S.id_support
from (
Select id_cust, count(*) as counter
from rc_sortir_intag
group by id_cust) CC
on C.id_cust = CC.id_cust
inner join Support S
on CC.couter = S.role
where not exists (select 1
from Customer C
where C.id_cust = CC.id_cust)
end
Test it !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ok thanx a lot for that clue it works...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply