how to update - 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 2005 Forums

Transact-SQL (2005)

how to update

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-09 : 14:12:01

I have two tables on database, tbla and tblb
On tbla's has map drive (default is z:\abc\xyz) data.

now i have to update on tblb where tbla.id=tblb.id but i don't need tbla's default path. I mean z: up to \xyz

on tblb, update full_path which "d:\" + tbla's path + tblb's file_name
e.g. d:\aa\testin.xml ---the first record's output looks like this.

tbla looks like this:
id, path
1 z:\abc\xyz\aa
2 z:\abc\xyz\mnop\sss
3 z:\abc\xyz\123\55\667

tblb looks like this:
id,file_name full_path
1 testin.xml
2 result file.doc
3 aa bb cc.txt

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-09 : 15:00:23

Guys, anybody have an idea??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 15:15:27

UPDATE b
SET b.full_path = 'd:\' + substring(a.path, 4, datalength(a.path)) + b.file_name
FROM tableb as b
inner join tablea as a on a.id = b.id

E 12°55'05.63"
N 56°04'39.26"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-09 : 15:27:16

thanks Peso,
I got following error:

Msg 402, Level 16, State 1, Line 1
The data types nvarchar and ntext are incompatible in the add operator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 15:31:38

Cast the NTEXT column as NVARCHAR(300).
Please remember I can't tell which column because you haven't given that information.

E 12°55'05.63"
N 56°04'39.26"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-09 : 15:36:15

on tbla:
Id as int
Path as nvarchar(255)

On tblb:
Id as int
File_name as nvarchar(255)
Full_Path as nvarchar(255)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 15:39:58

None of them are NTEXT column.
Are you sure it's this UPDATE statement that fails?

E 12°55'05.63"
N 56°04'39.26"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-09 : 15:41:35

yes, i have to update on Full_Path (it is on tblb)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-09 : 15:44:46

rudba,

Show your actual statement that you have used. ALSO are you sure about datatype of columns? Cast as nvarchar(300) if ntext datatype as Peso Said.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-09 : 15:49:16

I got it thanks guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources