need SQL help - 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

General SQL Server Forums

New to SQL Server Programming

need SQL help

Author

Topic

safderalimd
Starting Member

32 Posts

Posted - 2007-01-18 : 16:11:41

I need your help to write SQL to accomplish below task.

Table I

col1 col2

3 2

4 2

1 3

6 3

Table1 col1 value is old value to be repalced with col2 value

Apply above replacement to table II

Current TABLE2
col1

[2] [3] [4] [5]

[3] [4] [5] [6]

[1][6]

[2] [3] [4] [9]

Table2 should look like
col1

[2] [5]

[2] [5] [3]

[3]

[2] [9]

Note: final value should not have repeated values like [2] [2] [5] … It should be [2] [5]

More info:

the column stores data like this

[1],[2],[3],[4]

Say 2, 3 are being put into 2 so the row becomes

[1],[2],[4]

Or say for instance you have this

[1],[2],[3],[4]

But 2, 4 are becoming 5 then it should go

[1],[5],[3]"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-18 : 18:41:07

Before I (or anyone) work on this, please confirm that this script correctly represents your sample data (you should post a script like this when you post questions, it makes it much clearer to understand the question and you'll get quicker responses because one of us won't have to do this for you).

create table #Table1
(col1 int,
col2 int)
create table #Table2
(col1 varchar(50))
insert #Table1
select 3, 2 union all
select 4, 2 union all
select 1, 3 union all
select 6, 3
insert #Table2
select '[2],[3],[4],[5]' union all
select '[3],[4],[5],[6]' union all
select '[1],[6]' union all
select '[2],[3],[4],[9]'

select * from #Table1
select * from #Table2

drop table #Table1
drop table #Table2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 02:02:01

Like this?

-- prepare sample data
create table	#Table1
		(
			col1 int,
			col2 int
		)

insert	#Table1
select	3, 2 union all
select	4, 2 union all
select	1, 3 union all
select	6, 3

create table	#Table2
		(
			col1 varchar(50)
		)

insert	#Table2
select	'[2],[4],[3],[5]' union all
select	'[6],[4],[3],[5]' union all
select	'[1],[6]' union all
select	'[2],[9],[4],[3]'

-- show the old data
select * from #table2

-- Here is the trick!!!
declare @stage table (cs int, p varchar(50))

declare @currcs int,
	@maxcs int,
	@col varchar(8000)

select	@currcs = min(checksum(col1)),
	@maxcs = max(checksum(col1))
from	#table2

while @currcs <= @maxcs
	begin
		select	@col = col1
		from	#table2
		where	checksum(col1) = @currcs

		insert	@stage
		select	checksum('#' + @col),
			data
		from	dbo.fnParseList(',', @col)

		select	@currcs = min(checksum(col1))
		from	#table2
		where	checksum(col1) > @currcs
	end

update		s
set		s.p = '[' + cast(t1.col2 as varchar) + ']'
from		@stage as s
inner join	#table1 as t1 on '[' + cast(t1.col1 as varchar) + ']' = s.p

update	#table2
set	col1 = '#' + col1

select	@currcs = min(cs),
	@maxcs = max(cs)
from	@stage

while @currcs <= @maxcs
	begin
		select	@col = null

		select	@col = isnull(@col + ',', '') + x.p
		from	(
				select distinct	top 100 percent p
				from		@stage
				where		cs = @currcs
				order by	p
			) as x

		update	#table2
		set	col1 = @col
		where	checksum(col1) = @currcs

		select	@currcs = min(cs)
		from	@stage
		where	cs > @currcs
	end

-- show the new data
select * from #table2

drop table #Table1
drop table #Table2

Here is the function

CREATE FUNCTION dbo.fnParseList
(
	@Delimiter CHAR,
	@Text VARCHAR(8000)
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
	DECLARE	@NextPos SMALLINT,
		@LastPos SMALLINT

	SELECT	@NextPos = 0

	WHILE @NextPos <= DATALENGTH(@Text)
		BEGIN
			SELECT	@LastPos = @NextPos,
				@NextPos =	CASE
							WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
							ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
						END

			INSERT	@Result
				(
					Data
				)
			SELECT	SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
		END
		
	RETURN
END

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources