如何使用SQL脚本对列表使用“更新和替换” [英] How do I use Update and Replace to a column table using SQL Script

问题描述

我有一个如下脚本，它试图用SQL脚本来改变表中字符串的一部分。脚本执行时没有错误，但不会用新路径替换旧路径

路径示例为'\\John-PC\reportfolder\DR-REport \ Report 12.02 13.13.doc'



谢谢



使用ReportConfig

GO



DECLARE @OldPath varchar（30），@ NewPath varchar（30）

SET @OldPath ='\\John-PC \'

SET @NewPath ='\\Domin-Win7-PC \'





UPDATE ReportSetup

SET Path = REPLACE（Path，@ OldPath，@ NewPath）WHERE Path IS NOT NULL

AND WHERE Path LIKE @OldPath

GO

解决方案

Albert_optima写道：

AND WHERE路径LIKE @OldPath

这只会更新路径列完全等于 \\John-的记录PC \ 。



另外，你可以那里没有第二个 WHERE 。



假设您要更新的所有路径从 \\John-PC \ 开始，尝试：

 更新 ReportSetup 
  SET 路径= REPLACE（路径， @ OldPath ， @ NewPath ）
  WHERE 路径 IS   NOT   NULL  
  AND 路径 LIKE   @ OldPath  + '  ％' 

当我使用如下的AND子句时，它会抱怨WHERE是不正确的语法

靠近WHERE即AND WHERE路径LIKE @OldPath +'％'



它可以工作，如果我删除'AND'子句并使用它如下

SET路径= REPLACE（路径，@ OldPath，@ NewPath）WHERE路径LIKE @OldPath +'％'



你知道为什么吗？

感谢您的快速回复

引用：

WHERE路径不是NULL

AND WHERE路径LIKE @OldPath

不是我以前见过的语法。尝试

  WHERE 路径 IS   NOT   NULL  
  AND 路径 LIKE   @ OldPath  + ' ％'< /跨度> 

I have a script as below which am trying to use to change part of a string in a table using SQL script. The script executes without error but does not replace old path with new path
The path example is '\\John-PC\reportfolder\DR-REport\Report 12.02 13.13.doc'

Thanks

Use ReportConfig
GO

DECLARE @OldPath varchar(30), @NewPath varchar(30)
SET @OldPath = '\\John-PC\'
SET @NewPath = '\\Domin-Win7-PC\'


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

解决方案

Albert_optima wrote:

AND WHERE Path LIKE @OldPath

This will only update records where the Path column is exactly equal to \\John-PC\.

Also, you can't have a second WHERE in there.

Assuming you want to update all paths which start with \\John-PC\, try:

UPDATE ReportSetup
SET Path = REPLACE(Path, @OldPath, @NewPath) 
WHERE Path IS NOT NULL
AND Path LIKE @OldPath + '%'

When I use the AND clause as below it complains the WHERE is incorrect syntax
near the "WHERE" i.e. "AND WHERE Path LIKE @OldPath + '%'"

It works if I remove the 'AND' clause and use it as below
SET Path = REPLACE(Path,@OldPath,@NewPath) WHERE Path LIKE @OldPath + '%'

Do you know why?
Thanks for the quick response

Quote:

WHERE Path IS NOT NULL
AND WHERE Path LIKE @OldPath

is not a syntax I've seen before. Try

WHERE Path IS NOT NULL
 AND Path LIKE @OldPath + '%'

这篇关于如何使用SQL脚本对列表使用“更新和替换”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！