为什么这两个替换中的第一个没有生效?

Question

为什么

UPDATE Playlists 
SET PlaylistName = replace(PlaylistName,'TDJ','TDJe'),
PlaylistName = replace(PlaylistName,'TDJe','TDJxxx')

更换TDJ失败?

考虑到我可能需要很多替代品，什么是好的紧凑修复?

Answer 1

在UPDATE语句中，任何出现在=右侧的列值(例如PlaylistName)都具有原始值。它相当于以下命令式代码:

for each row:
    old = playlistName
    new1 = replace(old,'TDJ','TDJe')
    new2 = replace(old,'TDJe','TDJxxx')
    playlistName = new1
    playlistName = new2

尝试更新一个列两次是没有意义的。

您必须在单个表达式中进行所有替换:

UPDATE Playlists
SET PlaylistName = replace(replace(PlaylistName, 'TDJ', 'TDJe'), 'TDJe', 'TDJxxx')