我现在想把这个表里HT_NUMBER字段更新成INT型的,于是我的语句这样写,但是系统提示错误。请问大家错在哪里?或者有另外的写法。
--我先查询出来。结果是正常的。
SELECT 65273+(ROW_NUMBER() OVER (ORDER BY HT_NUMBER)) FROM C41
WHERE HT_NUMBER LIKE '%[^0-9]%'
--下面进行更新。这条语句提示错误:开窗函数只能出现在 SELECT 或 ORDER BY 子句中。
UPDATE C41 SET HT_NUMBER=65273+(ROW_NUMBER() OVER (ORDER BY HT_NUMBER))
WHERE HT_NUMBER LIKE '%[^0-9]%'请问大家,该怎么做? 我这条语句错在哪里?光速结贴!!!
WHERE HT_NUMBER LIKE '%[^0-9]%'
--UPDATE C41 SET HT_NUMBER=65273+(ROW_NUMBER() OVER (ORDER BY HT_NUMBER))
--WHERE HT_NUMBER LIKE '%[^0-9]%'
--ROW_NUMBER出错!
declare @id int
select HT_NUMBER,ROW_NUMBER() VOER (ORDER BY HT_NUMBER) FROM C41
UPDATE C41 SET HT_NUMBER = 65273 + @id
WHERE HT_NUMBER LIKE '%[^0-9]%'
;with cte as
(
SELECT HT_NUMBER,65273+(ROW_NUMBER() OVER (ORDER BY HT_NUMBER)) as rn FROM C41
WHERE HT_NUMBER LIKE '%[^0-9]%'
)
update cte set HT_NUMBER=rn
(
SELECT HT_NUMBER,65273+(ROW_NUMBER() OVER (ORDER BY HT_NUMBER)) as rn FROM C41
WHERE HT_NUMBER LIKE '%[^0-9]%'
)
update cte set HT_NUMBER=ltrim(rn)
--如果HT_NUMBER是字符型价格ltrim隐式转换下
不好意思,写错了。declare @id int
select @id = ROW_NUMBER() VOER (ORDER BY HT_NUMBER) FROM C41
UPDATE C41 SET HT_NUMBER = MAX(HT_NUMBER) + @id
WHERE HT_NUMBER LIKE '%[^0-9]%'