How to update a table in Oracle SQL

Question

So trying to update an empty column that I just created with the script below. Whenever I try to run it I get the following error message (Query 1):

ORA-00933: SQL command not properly ended

So I googled around a bit and tried using a slightly different query (Query 2) but then get the following error message:

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

Anyone know why this might be?

Query 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;

Query 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);

possible duplicate of [SQL Error: ORA-00933: SQL command not properly ended](http://stackoverflow.com/questions/8940471/sql-error-ora-00933-sql-command-not-properly-ended) — ruudvan, Mar 10 '15 at 17:30

score 0 · Answer 1 · answered Mar 11 '15 at 12:27

try this:

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)

How to update a table in Oracle SQL

1 Answers1