updating a table - 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)

updating a table

Author

Topic

kingbee
Starting Member

2 Posts

Posted - 2006-01-21 : 15:04:16

I have table with following 5 fields

ID, Type, Code, Value, Pos.

I have to update Value and Pos fields depending on code and type fields.

1)If two rows have same code value and type values are ADD and DELETE then update Value with A and B and Pos with 1 and 2.
if i has another two rows with same scenario then do same thing as above but the Pos values must be 3 and 4.
and so on...

I hope i explained it well.How you do it in a stored procedure preferably without cursors (because of large amount of data).

Thanks for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-21 : 23:50:19

Maybe if you can post some sample data and expected result ? It will certainly help us to understand your requirement better.

-----------------
'KH'

kingbee
Starting Member

2 Posts

Posted - 2006-01-21 : 23:52:31

If i have following data in the data (in this table we will always have only two rows with same Code value)

ID Type Code Value Pos
1 ADD S1 NULL NULL
2 ADD S2 NULL NULL
3 DELETE S2 NULL NULL
4 DELETE S1 NULL NULL
5 ADD S3 NULL NULL
6 ADD S3 NULL NULL

In above table i have S1 and S2 codes have ADD and DELETE type values and S3 has both ADD values.So i want to update Value and Pos values as follows

ID Type Code Value Pos
1 ADD S1 A 1
2 ADD S2 A 3
3 DELETE S2 B 4
4 DELETE S1 B 2
5 ADD S3 X NULL
6 ADD S3 X NULL

In words if a code has both ADD and DELETE then i want to update the Value and Pos so that ADD row has Vlaue A and Pos 1 and DELETE row has Value B and Pos 2.If another code has both ADD and DELETE then Value is same but Pos values should be 3 and 4.if another Code has those two types then Values are same but Pos should be 5 and 6.we have to increment Pos values for evry code.
We don't update Pos column if we don't have both ADD and DELETE.

I hope i gave enough details..

Thanks for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-22 : 00:52:15

I make use of a temp table #temp to store the rows that required changes and identity column for the Pos.

create table #data
(
	ID	int,
	Type	varchar(10),
	Code	varchar(10),
	Value	varchar(10),
	Pos	int
)

create table #temp
(
	ID	int,
	Value	varchar(10),
	Pos	int identity(1,1)
)

delete #data
insert into #data(ID, Type, Code, Value, Pos)
select 1, 'ADD', 	'S1', NULL, NULL	union all
select 2, 'ADD', 	'S2', NULL, NULL	union all
select 3, 'DELETE', 	'S2', NULL, NULL	union all
select 4, 'DELETE', 	'S1', NULL, NULL	union all
select 5, 'ADD', 	'S3', NULL, NULL	union all
select 6, 'ADD', 	'S3', NULL, NULL

insert into #temp (ID, Value)
select 	d.ID, case when d.Type = 'ADD' then 'A' else 'B' end
from	#data d
where	d.Code in (select x.Code from #data x where x.Code = d.Code and Type = 'ADD')
and	d.Code in (select x.Code from #data x where x.Code = d.Code and Type = 'DELETE')
order by d.Code, d.Type

update	#data
	set	Value	= t.Value,
		Pos	= t.Pos
	from	#data d inner join #temp t
	on	d.ID	= t.ID

select * from #data

drop table #data
drop table #temp

-----------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources