Update statement - 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)

Update statement

Author

Topic

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-01-31 : 05:45:27

Hi,

I have parent table with the columns

ID (P.Key) FilePath
1 /documents/articles
2 /images/movies
3 /letters/January

And another table that needs to be updated
Url FilePathID
http://sitename//documents/articles Null
http://sitename/images/movies Null
http://sitename/letters/January Null

The FilePath ID should be 1,2,3 respectively.

Instead of writing a number of Update statements I want a single Update statement , I tried with Union but got errors.

Do you have any other option.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 05:52:01

update t2
set t2.filepathid = (select t1.id from table1 as t1 where t2.url like '%' + t1.filepath)
from table2 as t2

Peter Larsson
Helsingborg, Sweden

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-01-31 : 07:12:03

Thanks it works great

quote:
Originally posted by Peso

update t2
set t2.filepathid = (select t1.id from table1 as t1 where t2.url like '%' + t1.filepath)
from table2 as t2

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources