CODEWITHSUNDEEP
sql-server-2008
addcom
addcom

Reputation: 1

How to declare variable In T-SQL and use it in update statement

New to SQL scripting, trying to update part of a string in SQL script using define variable as below but can't get it to work. The column can be like \\OM-WD08-1\TestDoc\report\rep.pdf there are lot of table with different column heading to update. I want to declare the variable and substitute where applicable. thanks in advance 

USE MyServer

-- Declare the variable to be used.
DECLARE @OldPath varchar(30), @NewPath varchar(30)

-- Initialize the variable
SET @OldPath ='\\OM-WD08-1\';
SET @NewPath ='\\AA-PC\';

UPDATE AnatomyConfigs
SET Path = REPLACE(Path, @OldPath, @NewPath) 
WHERE Path IS NOT NULL
  AND Path LIKE @OldPath
GO

Upvotes: 0

Views: 77

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

LIKE only performs a literal match of text unless you include some form of wildcards (% or _). I would do this:

USE MyServer

-- Declare the variable to be used.

DECLARE @OldPath varchar(30), @NewPath varchar(30)

-- Initialize the variable
SET @OldPath ='\\OM-WD08-1\';
SET @NewPath ='\\AA-PC\';

UPDATE AnatomyConfigs
    SET Path = @NewPath + SUBSTRING(Path,LEN(@OldPath)+1,8000)
WHERE Path LIKE @OldPath + '%'
GO

I prefer to use SUBSTRING over REPLACE because it more cleanly reflects "I just want to replace an instance that occurs at the start of the string". Now, given that no path should contain \\s in the middle, this is not really required in this specific case, but I'm going for a more general answer.

Upvotes: 1

Nadendla
Nadendla

Reputation: 722

Declare @varibale_name varhcar(50)
To set Value:
set @variable_name='dgnughg'

update Table_Name set column_name=@variable_name

Upvotes: 0

Related Questions