Re: Why doesn't this update statement work?

"ck388" <google_at_timyee.com> wrote in message news:698a71bb.0403201824.5452fbd6_at_posting.google.com...
> update TOE_SER_STOPGAP B INNER JOIN TOE_SER_STOPGAP A ON A.TICKETNUM =
> B.TICKETNUM and A.CRIS3SEQ = B.CRIS3SEQ
> set B.HOLDCODES = A.HOLDCODES
>
> I'm simply testing out how to use JOINS with an update statement but
> even the simplest one I can think of does not work (ie just updating
> the table with the same information). I get an error "Missing SET
> Keyword".
>
> Anyone have any idea why?
>
> Thanks,
>
> Tim :-)

This is more like a RTFM question.
Apart from that, you should tell us the Oracle version you are trying this on! .. and posting this question on comp.databases.oracle.server is enough

An example of what you are looking for (in 9.2.0.3):

SQL> create table mytab (pkcol number, col2 varchar2(10), constraint pkcol_pk primary key (pkcol));

Table created.

SQL> insert into mytab values (1, 'ABCD');

1 row created.

SQL> insert into mytab values (2,'MNOP');

1 row created.

SQL> commit;

Commit complete.

SQL> update (select t1.col2 t1_col1, t2.col2 t2_col2 from mytab t1, mytab t2 where t1.pkcol = t2.pkcol)
  2 set t1_col1 = t2_col2
  3 /

2 rows updated.

SQL> select * from mytab;

     PKCOL COL2
---------- ----------

         1 ABCD
         2 MNOP

Anurag Received on Sat Mar 20 2004 - 21:38:19 CST