如何在 Oracle SQL 中更新表

Question

所以尝试更新我刚刚使用以下脚本创建的空列。每当我尝试运行它时，我都会收到以下错误消息（查询 1）：

ORA-00933: SQL command not properly ended

所以我用谷歌搜索了一下，并尝试使用稍微不同的查询（查询 2），但随后收到以下错误消息：

ORA-00904: "T1"."PLAYED_PREVIOUS_YEAR": invalid identifier

有人知道为什么会这样吗？

查询 1：

update JWxsellallprod t1
   set t1.played_previous_year = case when t2.cust_id IS NOT NULL then 1 else 0 end
 from JWxsellallprod t1
 left join (select a.Year_of_Play,
                   a.Year_Aquired,
                   a.cust_id
              from JWxsellallprod a
             inner join JWxsellallprod b
                on b.cust_id = a.cust_id
             where a.player_days > 0
               and b.player_days > 0
               and b.Year_of_Play = a.Year_of_Play - 1
               and a.Year_Aquired = b.Year_Aquired
           ) t2
   on t2.cust_id = t1.cust_id
  and t2.Year_of_Play = t1.Year_of_Play
  and t2.Year_Acquired = t1.Year_Acquire;

查询 2

update JWxsellallprod t1
   set t1.played_previous_year
      = (select case when t2.cust_id IS NOT NULL then 1 else 0 end
           from JWxsellallprod t1
           left join (select a.Year_of_Play,
                             a.Year_Aquired,
                             a.cust_id
                        from JWxsellallprod a
                       inner join JWxsellallprod b
                          on b.cust_id = a.cust_id
                       where a.player_days > 0
                         and b.player_days > 0
                         and b.Year_of_Play = a.Year_of_Play - 1
                         and a.Year_Aquired = b.Year_Aquired
                     ) t2
             on t2.cust_id = t1.cust_id
            and t2.Year_of_Play = t1.Year_of_Play
            and t2.Year_Acquired = t1.Year_Acquire);

Answer 1

试试这个：

update JWxsellallprod a
set played_previous_year = (
  select sign(count(*))
  from JWxsellallprod b
  where a.player_days > 0 and b.player_days > 0
    and b.Year_of_Play = a.Year_of_Play - 1
    and a.Year_Aquired = b.Year_Aquired
    and a.cust_id = b.cust_id)