使用with子句更新语句 [英] Update statement using with clause

问题描述

我有一个脚本，该脚本使用一堆with子句得出一些结果，然后将结果写到表中.我只是无法理解，有人可以指出我正确的方向吗?

I have a script that uses a stack of with clauses to come up with some result, and then I want to write that result in a table. I just can't get my head around it, could someone point me in the right direction?

这是一个简化的示例，指示我要执行的操作:

Here's a simplified example that indicates what i want to do:

with comp as (
  select *, 42 as ComputedValue from mytable where id = 1
)
update  t
set     SomeColumn = c.ComputedValue
from    mytable t
        inner join comp c on t.id = c.id 

真实的东西有很多互相引用的with子句，因此，实际使用with子句的任何建议都比将其重构为嵌套子查询更可取.

The real thing has quite a few with clauses that all reference each other, so any suggestions actually using the with clause would be highly preferred over refactoring it to nested subqueries.

预先感谢

Gert-Jan

推荐答案

如果有人跟在我后面，这就是对我有用的答案.

If anyone comes here after me, this is the answer that worked for me.

update mytable t
set z = (
  with comp as (
    select b.*, 42 as computed 
    from mytable t 
    where bs_id = 1
  )
  select c.computed
  from  comp c
  where c.id = t.id
)

祝你好运

GJ

这篇关于使用with子句更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！