Update using subqueries - 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

Transact-SQL (2000)

Update using subqueries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-05 : 07:45:29

Mich writes "I want to update the value of an item in one row with the value of an item in another row in the same table...

PD_TEST
Memberno contribution ID oldprojectcode projectcode
100 1 NULL A
100 NULL A B
100 NULL B C

I want to set the contribution id to be the same for all links in the chain. each record is linked to a previous record by oldprojectcode and projectcode, except for the first one in the chain which has the contribution id.

Can I do this in a single SQL statement? I want to avoid using a cursor if possible as there are 500,000 rows.

I'm using sqlserver 6.5 "

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-05 : 12:05:32

Is MemberNo the identifier?

create table #PD_TEST (Memberno int, contributionID int,oldprojectcode char(1), projectcode char(1))
go
insert into #PD_TEST  
select 100, 1, NULL,'A' union all
select 100, NULL,'A','B' union all
select 100, NULL,'B','C' union all
select 200, 2, NULL,'A' union all
select 200, NULL,'B','C'union all
select 300, 3,NULL,'A' 
go
--select * from #PD_TEST

update #PD_TEST set contributionid = a.contributionid 
from #PD_TEST p,
(
	select memberno,contributionid 
	from #PD_TEST 
	where contributionid is not null
) a
where p.contributionid is null
and a.memberno = p.memberno
go
select * from #PD_TEST
go
drop table #PD_TEST

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-05 : 12:18:49

Hi ehorn,

I read the requirement as follows (and could be wrong about this but...)

For a given Memberno (like 100) find a NOT NULL value in the contributionID column (in this case, the value is 1).

This value of '1' is used to update all columns 'contributionID' in all the rows of Memberno(100) which follow the 'chain'. (This chain makes what should be a simple problem unnecessarily complex - or impossible(?) to solve without a CURSOR)

Row 2 in the example is part of the chain because oldprojectcode(A) = projectcode(A) of the first row.

Row 3 in the example is part of the chain because oldprojectcode(B) = projectcode(B) of the second row.

And so on.

I'm not sure if a single Memberno can have more than one chain from Mich's description. I am sure that I'd like to add another column to uniquely identify a chain.

Maybe there's an easy way to solve this problem with a set-based solution. Where is Mr. Cross Join when you need him anyway?

I've subscribed to see what comes of it.

Sam

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-05 : 15:04:07

quote:

I'm not sure if a single Memberno can have more than one chain from Mich's description. I am sure that I'd like to add another column to uniquely identify a chain.

Agreed...

Missing a few details on the requirements.

Such is life...

If some combination MemberNo,oldprojectcode,projectcode are the determinates maybe an adjacency/nested set could be used as a derived table to perform the update ? ? ?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-08 : 10:49:52

Can't think up anything better than this:

create table #t (v int, old char(1), new char(1))

insert into #t
select 5, 'a', 'a' union all
select 0, 'a', 'z' union all
select 0, 'z', 'm' union all
select 0, 'm', 'b' union all
select 0, 'b', 'q' union all
select 7, 'j', 'j' union all
select 0, 'j', 'o' union all
select 0, 'o', 'k' union all
select 0, 'k', 's'

while @@rowcount>0
update #t set v=(select t.v from #t t where t.new=#t.old)
where v=0 and (select t.v from #t t where t.new=#t.old)>0

select * from #t
drop table #t

BTW, in MS Access it can done with one single UPDATE, but
only if the table was populated by rows in "right" order:

update t set v=DLookUp("v", "t", "new=" & "'" & old & "'")

Apparently, because Access "commits transaction" on ROW-level.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources