在teradata 16.20中,有没有一种方法可以从两个CTE进行更新或合并?

kx7yvsdv  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(470)

在teradata 16.20中,有没有一种方法可以更新或合并两个CTE?
例如,在mssql中,我们有第一个cte,第二个cte使用第一个cte,然后更新:

  1. with CTE1 as (
  2. select alpha, beta
  3. from someTable a
  4. join otherTable b on a.aleph = b.aleph
  5. ), CTE2 as (
  6. select beta, gamma
  7. from CTE1 c
  8. join anotherTable d on c.alpha = d.alpha
  9. )
  10. update u
  11. set u.gamma = e.gamma
  12. from updateTable u
  13. join CTE2 e on u.beta = e.beta;

在teradata 16.20中,这当然适用于一个cte,如下所示:

  1. merge into mydb.mytable
  2. using (
  3. select alpha, beta
  4. from someTable a
  5. join otherTable b on a.aleph = b.aleph
  6. ) as CTE (alpha, beta)
  7. on mytable.alpha = CTE.alpha
  8. when matched then update
  9. set beta = CTE.beta;

有没有办法用两个或更多的CTE来实现这一点?

mcvgt66p

mcvgt66p1#

不能在派生表中使用with(cte)(这是 USING 合同条款 MERGE 语句),但可以使用嵌套的派生表:

  1. merge into mydb.mytable u
  2. using (
  3. select beta, gamma
  4. from (
  5. select alpha, beta
  6. from someTable a
  7. join otherTable b on a.aleph = b.aleph
  8. ) CTE1
  9. join anotherTable d on CTE1.alpha = d.alpha
  10. ) CTE2
  11. on u.beta = CTE2.beta
  12. when matched then update
  13. set gamma = CTE2.gamma;

或者如果 MERGE 不适用于(例如联接 predicate 不包括所有主索引列)使用联接的相同方法 UPDATE :

  1. UPDATE u FROM mydb.mytable u,
  2. (
  3. select beta, gamma
  4. from (
  5. select alpha, beta
  6. from someTable a
  7. join otherTable b on a.aleph = b.aleph
  8. ) CTE1
  9. join anotherTable d on CTE1.alpha = d.alpha
  10. ) CTE2
  11. set gamma = CTE2.gamma
  12. WHERE u.beta = CTE2.beta;
展开查看全部

相关问题