与子查询相关的 DB2 SQL 更新 [英] DB2 SQL update correlating with subquery

问题描述

代码块:

update yrb_purchase px 
  set px.club = (select club
                   from (select p.title, p.year, o.club, o.price, 
                                ROW_NUMBER() OVER(PARTITION BY p.title, p.year 
                                                  ORDER BY o.price
                                                 ) rn 
                          from yrb_purchase 
                          inner join yrb_offer o    on p.title = o.title
                                                   and p.year = o.year
                          inner join yrb_member m   on m.club = o.club
                          inner join yrb_customer c on c.cid = p.cid
                                                   and c.cid = m.cid
                          where p.cid = px.cid 
                            and p.title = px.title
                            and p.year = px.year
                          order by title
                        ) 
                 where rn = 1
              ) 
 where ....

我的问题是:在执行上述代码时，我得到'SQL0204N "PX.YEAR" is an undefined name.SQLSTATE=42704'.我的印象是，在这种情况下，更新查看的行将传递到内部查询中.我做错了什么?我得到的例子是:

My issue is thus: upon execution of the above code, I get 'SQL0204N "PX.YEAR" is an undefined name. SQLSTATE=42704'. I was under the impression that in this case, the row being looked at by update would be passed into the inner query. What have I done wrong? The example I was given was:

update yrb_purchase P
set club = (
        select min(M.club)
            from yrb_member M, yrb_offer O
            where P.cid = M.cid
                and M.club = O.club
                and P.title = O.title
                and P.year = O.year
    )
where
    club <> (
        select min(M.club)
            from yrb_member M, yrb_offer O
            where P.cid = M.cid
                and M.club = O.club
                and P.title = O.title
                and P.year = O.year
    );

这是一个错误代码的例子，但代码应该可以工作.

which was given as an example of bad code, but code that should work nonetheless.

推荐答案

对 DB2 (9.7 LUW) 进行的快速测试表明，这在一个子查询级别内有效，但不会超过一个级别.这有效:

A quick test with DB2 (9.7 LUW) reveals that this works within one level of subquery, but not more than one. This works:

update foo f
   set bar = ( 
       select count(*) from baz where f.bar = baz.bar
   )

这不起作用:

update foo f
   set bar = ( 
       select count(*) from (
           select * from baz where f.bar = baz.bar
       )
   )

这是为什么?谁知道.这只是 DB2 语法的众多谜团之一.

Why is this? Who knows. It is just one of many mysteries of DB2 syntax.

无论如何，这是一种糟糕的编码风格(就像给你例子的人暗示的那样).您应该 当你想根据其他表的内容进行更新时使用MERGE:

In any case, this is a bad style of coding (just like the person who gave you the example implied). You should use MERGE when you want to do an update based on the contents of other tables:

merge into foo f
    using baz b 
    on foo.bar = baz.bar
    when matched then update set
        foo.bar = 123;

这篇关于与子查询相关的 DB2 SQL 更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！