DML Scenario - 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 2000 Forums

SQL Server Development (2000)

DML Scenario

Author

Topic

44443
Starting Member

10 Posts

Posted - 2005-06-21 : 11:56:05

Table A

srcdata seqid srcdata1
_________________________
1001 1 null (2001 2002 2020)
1009 2 null
1100 3 null

Table B

srcdata1 seqid
______________________
2001 1
2002 1
2030 1

I have the above scenario where I need to update srcdata1 column in Table A with seqid column in TableA
and TableB

In the above example since the seqid 1 has 3 rows in Table B I should update srcdata1 in Table A with
values as (2001 2002 2030)

Any thoughts on how this can be achieved, i tried using stored procedure to incoporate the logic to do
the above but seem to work

Appreciate your help

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-21 : 12:54:13

here you go...
but this is not good design.
why do you need this????


create function Contencate ( @separator varchar(3), @seqid int)
returns varchar(100)
as
begin
Declare @List varchar(100)
SELECT 	@List = COALESCE(@List + @separator, '') + srcdata1 
FROM 	tB
WHERE 	seqid = @seqid
order by srcdata1
return	@list
end 

go

create table tA (srcdata varchar(100), seqid int, srcdata1 varchar(100))
create table tB (srcdata1 varchar(100), seqid int)

insert into tA
select '1001', 1, null union all 
select '1009', 2, null union all
select '1100', 3, null

insert into tB
select '2001', 1 union all
select '2030', 1 union all
select '2002', 1

select * from tA
select * from tB

update 	tA 
set 	srcdata1 = dbo.Contencate(' ', tA.seqid )

select * from tA

go
drop table tA
drop table tB
drop function Contencate

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources