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 — ruudvan
– ruudvan, Commented Mar 10, 2015 at 17:30

Pavel Gatnar · Accepted Answer · 2015-03-11 12:27:48Z

0

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)

answered Mar 11, 2015 at 12:27

Pavel Gatnar

4,0532 gold badges22 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update a table in Oracle SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related