如何在update语句中解决select结果的开窗函数

[weixin_52728846]

这是我的select 语句

select '402'+right('00000' + (cast((row_number() over ( order by getdate())) as varchar(6))),4) as 新商品编号,* 
from 商品表a
join  商品表b on a.id=b.id
where b.商品类别='112'

select 的结果已经达到我要结果，就是把商品表a中的旧商品编号替换成新的商品编号（按402+0001开始递升）

我的目的是想能够这样update这个select结果

update a set 商品编号= '402'+right('00000' + (cast((row_number() over ( order by getdate())) as varchar(6))),4)
from商品表a
join  商品表b on a.id=b.id
where b.商品类别='112'

但是结果提示：开窗函数只能出现在 SELECT 或 ORDER BY 子句中。

还请请求各位大神指导一二，谢谢了！

 

[weixin_52728846]

@IEEE_China 可以深入一点么，初学自学者有点吃力

[IEEE_China]

使用 WITH common_table_expression 子句 参考文档 https://docs.microsoft.com/zh-cn/sql/t-sql/queries/update-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 with cte as( select 语句 ) update 语句

[IEEE_China]

示例

declare @dta as table(
    姓名 nvarchar(10),
    性别 nvarchar(10)
)

declare @dtb as table(
    性别 nvarchar(10),
    数量 int
)

insert into @dta(姓名,性别)
select '张三','男' union all 
select '赵二','男' union all 
select '王五','男' union all 
select '周六','女' 

insert @dtb (性别,数量)
select '男',0 union all
select '女',0

---    查看
select * from @dta
select * from @dtb 

---    更新
;with cte(sex,cnt) as(
    select 性别,COUNT(1) from @dta group by 性别
)
update @dtb set 数量=cnt  from cte as c where 性别=c.sex

---    查看
select * from @dtb 

结果

(4 行受影响)

(2 行受影响)
姓名         性别
---------- ----------
张三         男
赵二         男
王五         男
周六         女

(4 行受影响)

性别         数量
---------- -----------
男          0
女          0

(2 行受影响)

(2 行受影响)

性别         数量
---------- -----------
男          3
女          1

(2 行受影响)

[IEEE_China]

@weixin_52728846 无论你select语句怎么写的，放到cte里，update更新数据 from cte where 条件 ，有那么难吗

[weixin_52728846]

好像举例的和我表达不太一样呢，还不是很明白

[xuzuning]

开窗函数太新，已超出我的知识范围，我只知道如果使用当前表字段加工而城的数据修改表字段，是违规的。因为极易出现死循环，至少需要一个自联接，伪装成两个表来骗过 sql 的语法检查