Updating field but not filename

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

Updating field but not filename

Author

Topic

kashy
Starting Member

12 Posts

Posted - 2007-01-22 : 06:36:29

I want to change the following field in the database - text field.

Want it to be like this,

C:\ProgramFiles\HEAT\HEATSelfService\attachments\Winter019997.htm

want it to be H:\2007\Winter019997.htm

My aim is to move the files from C:\ to H:\ drive and update the database to reflect this. Thanks

Thsi is my code;

declare @str varchar(1000)
UPDATE heatgen
set @str = 'c:\Program Files\HEAT\HEATSelfService\attachments\Winter019997.htm'
select reverse(@str)
select charindex('\', reverse(@str))
select right(@str, charindex('\', reverse(@str)))
select 'H:\2007' + right(@str, charindex('\', reverse(@str)))

SET gdetail = 'H:\2007' + RIGHT(gdetail, CHARINDEX('\', REVERSE(gdetail)))

The field is text I get error

Line 9: Incorrect syntax near '='.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 07:02:36

[code]UPDATE HeatGen
SET gDetail = 'h:\2007\' + case
when charindex('\', gDetail) > 0 then right(gDetail, charindex('\', reverse(gDetail)) - 1)
else gDetail
end[/code]
Peter Larsson
Helsingborg, Sweden

kashy
Starting Member

12 Posts

Posted - 2007-01-22 : 07:14:14

Still get the same error!

declare @str varchar(1000)
UPDATE HeatGen
set @str = 'c:\Program Files\HEAT\HEATSelfService\attachments\Winter019997.htm'
select reverse(@str)
select charindex('\', reverse(@str))
select right(@str, charindex('\', reverse(@str)))
select 'H:\2007' + right(@str, charindex('\', reverse(@str)))

SET gDetail = 'h:\2007\' + case
when charindex('\', gDetail) > 0 then right(gDetail, charindex('\', reverse(gDetail)) - 1)
else gDetail
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 07:17:30

No wonder!
Why are you mixing your non-working query with the suggestion I posted?

Run ONLY the code I suggested and it will work.

~~declare @str varchar(1000)~~
UPDATE HeatGen
set @str = 'c:\Program Files\HEAT\HEATSelfService\attachments\Winter019997.htm'
select reverse(@str)
select charindex('\', reverse(@str))
select right(@str, charindex('\', reverse(@str)))
select 'H:\2007' + right(@str, charindex('\', reverse(@str)))

SET gDetail = 'h:\2007\' + case
when charindex('\', gDetail) > 0 then right(gDetail, charindex('\', reverse(gDetail)) - 1)
else gDetail
end

Peter Larsson
Helsingborg, Sweden

kashy
Starting Member

12 Posts

Posted - 2007-01-22 : 07:26:25

Ok. Now getting these errors,

Server: Msg 8116, Level 16, State 2, Line 1
Argument data type text is invalid for argument 1 of reverse function.
Server: Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of right function.
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 07:30:55

Why are you using datatype TEXT for filenames that can be of maximum 256 characters long?

UPDATE	HeatGen
SET	gDetail = 'h:\2007\' +	case
					when charindex('\', gDetail) > 0 then right(CAST(gDetail AS VARCHAR(8000)), charindex('\', reverse(CAST(gDetail AS VARCHAR(8000)))) - 1)
					else gDetail
				end

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 07:38:55

Is this some kind of application built orignally in MS ACCESS?

Peter Larsson
Helsingborg, Sweden

kashy
Starting Member

12 Posts

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

No it is not MS Access. It is a Call logging front end to SQL Db.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 08:09:08

Please report on the status for the suggestion I provided twice.
Does it work?

Peter Larsson
Helsingborg, Sweden

kashy
Starting Member

12 Posts

Posted - 2007-01-22 : 08:32:44

Unfortunately I have not designed the Db that uses the text field. It does not work get errors as reported.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 08:37:12

Even when CASTing?

UPDATE	HeatGen
SET	gDetail = 'h:\2007\' +	cast(case
					when charindex('\', gDetail) > 0 then right(CAST(gDetail AS VARCHAR(8000)), charindex('\', reverse(CAST(gDetail AS VARCHAR(8000)))) - 1)
					else gDetail
				end as varchar)

Peter Larsson
Helsingborg, Sweden

kashy
Starting Member

12 Posts

Posted - 2007-01-22 : 08:49:00

That has changed everything!! BUT I only want fields to be changed that are c:\Program Files\.... not everything that this query has done!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 08:51:52

[code]UPDATE HeatGen
SET gDetail = 'h:\2007\' + cast(case
when charindex('\', gDetail) > 0 then right(CAST(gDetail AS VARCHAR(8000)), charindex('\', reverse(CAST(gDetail AS VARCHAR(8000)))) - 1)
else gDetail
end as varchar)
where gdetail like 'c:\program files\%'[/code]Are you a hapoy camper now?

Peter Larsson
Helsingborg, Sweden

kashy
Starting Member

12 Posts

Posted - 2007-01-22 : 09:19:04

Hmmm. This actually changes the whole field and the application loses the details relating to the attachments!

The field holds the data like this,

[Info] NumAttachments=1 [Attachments] Attachment1=65513|C:\Program Files\HEAT\HEATSelfService\attachments\Winter019997.htm

Now it is H:\2007\Winter019997.htm which is correct but the data is deleted on the application!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 10:17:04

Since you have not provided any information other than original posting, I assumed this is what you want to do.

quote:
Originally posted by kashy

My aim is to move the files from C:\ to H:\ drive and update the database to reflect this.

Peter Larsson
Helsingborg, Sweden

kashy
Starting Member

12 Posts

Posted - 2007-01-22 : 10:23:49

Do you have any thoughts on this? The way the field has its details? So as to retain the other details.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 10:27:02

Yes. Create a new column named "NewPosition" and update this column to reflect the new OS filepath.
Rename the gDetail column to "OldPosition".

UPDATE	HeatGen
SET	NewPosition = 'h:\2007\' +	case
						when charindex('\', gDetail) > 0 then right(CAST(gDetail AS VARCHAR(8000)), charindex('\', reverse(CAST(gDetail AS VARCHAR(8000)))) - 1)
						else gDetail
					end
where	gdetail like 'c:\program files\%'

Peter Larsson
Helsingborg, Sweden

kashy
Starting Member

12 Posts

Posted - 2007-01-22 : 11:20:29

I'm not sure of creating and renaming columns!!

kashy
Starting Member

12 Posts

Posted - 2007-01-23 : 09:38:58

I have not been able to do that! Any help appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-23 : 09:42:55