Replacing a value using a nested query

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)

Replacing a value using a nested query

Author

Topic

Steve_H
Starting Member

7 Posts

Posted - 2007-03-08 : 22:00:38

I have a table tempwebdata which holds an record for every field on a webpage.

The key field which links all records for a particular page is the the tempwebdataid.

I need to alter the value of one of the fields in each of a list of records. With me so far?

what I want to do is this:

select
replace (fieldvalue, ',9999999_864,' , ',-9999999_864,')
from tempwebdata
where tempwebdataid in
(select tempwebdataid from tempwebdata where fieldid = '0002075_2660')
and fieldid = '0';

thing is when I run the query ano rows are returned but when I run the nested select seperately I get the list of tempwebdataids I want.

the table has fields:
fieldid, fieldvalue, tempwebdataid

so for each web page saved I get many records in the tempwebdata table. 0002075_2660 is a fieldid I know has only been saved for the records with the tempwebdataids I want to change.

Obviously I want to run the query as an update once I can be sure that it is working correctly. Any help would be most gratefully received.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 22:03:58

your query will not return any reocrds

Your sub-query is returning tempwebdataid for fieldid = '0002075_2660' and your main query is looking for fieldid = '0' How could there be any match ?

Basically your query says give me record where fieldid equal to '0002075_2660' and also fieldid equal to '0' !

Steve_H
Starting Member

7 Posts

Posted - 2007-03-08 : 22:07:01

quote:
Originally posted by khtan

your query will not return any reocrds

Your sub-query is returning tempwebdataid for fieldid = '0002075_2660' and your main query is looking for fieldid = '0' How could there be any match ?

Basically your query says give me record where fieldid equal to '0002075_2660' and also fieldid equal to '0' !

KH

the tempwebdataid is the same for all records which pertain to the saved page - so if I have 100 fields on the webpage I will get 100 records in the tempwebdata table, all with the same tempwebdataid.

So what I want to do is find a list of the tempwebdataids which have fieldid '0002075_2660' and then change the fieldvalue where fieldid = '0' and the tempwebdataid is the same

Steve_H
Starting Member

7 Posts

Posted - 2007-03-08 : 22:18:15

For instance I could have a situation where I have

FieldID,Value,tempwebdataid
-------------------------------------------

1, "bob,jane,freddy", 75
2, "1 the street", 75
1, "Steve,Clare,freddy", 76
2, "2 the street", 76

I know that the only records I want to change have got a record with fieldid = 2 and freddy has changed his name to Sarah

so I get:

select
replace (value, ',freddy,' , ',sarah,')
from tempwebdata
where tempwebdataid in
(select tempwebdataid from tempwebdata where fieldid = '2')
and fieldid= '1';

see what I mean?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 22:25:21

try inner join


select *
from   tempwebdata a inner join tempwebdata b
       on a.tempwebdataid = b.tempwebdataid
where  a.fieldid = '0002075_2660'
and    b.fieldid = '0'

Steve_H
Starting Member

7 Posts

Posted - 2007-03-08 : 23:54:20

quote:

Originally posted by khtan

try inner join


select *
from   tempwebdata a inner join tempwebdata b
       on a.tempwebdataid = b.tempwebdataid
where  a.fieldid = '0002075_2660'
and    b.fieldid = '0'

that still gives me zero records

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 23:55:09

maybe try posting your table structure, some sample data and the result that you want.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-09 : 00:00:17

Here you go


declare @tempwebdata table
(
	FieldID		int,
	Value		varchar(20),
	tempwebdataid	int
)

insert into @tempwebdata
select	1, 'bob,jane,freddsy', 	75	union all
select	2, '1 the street', 	75	union all
select	1, 'Steve,Clare,freddy', 76	union all
select	2, '2 the street', 	76

select	b.*
from   	@tempwebdata a inner join @tempwebdata b
       	on a.tempwebdataid = b.tempwebdataid
where  	a.FieldID = 2
and    	b.FieldID = 1
/*
FieldID     Value                tempwebdataid 
----------- -------------------- ------------- 
1           bob,jane,freddsy     75
1           Steve,Clare,freddy   76
*/

select 	[result] = replace (Value, ',freddy,' , ',sarah,')
from 	@tempwebdata
where 	tempwebdataid in (select tempwebdataid from @tempwebdata where FieldID = 2)
and 	FieldID = 1
/*
result                                                                                                                                                                                                                                                           
----------------------
bob,jane,freddsy
Steve,Clare,freddy
*/

Steve_H
Starting Member

7 Posts

Posted - 2007-03-12 : 02:01:39

thanks loads for your help, using this code I have managed to solve my problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources