Why doesn't this update statement work?

[ck388]

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 :-)

[Anurag Varma]

"ck388" <google@timyee.com> wrote in message news:698a71bb.0403201824.5452fbd6@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

[ck388]

Worked like a charm. Thanks guy!

Tim :-)

"Anurag Varma" <avdbi@hotmail.com> wrote in message news:<L687c.5159$Ec6.3156@nwrddc02.gnilink.net>... "ck388" <google@timyee.com> wrote in message news:698a71bb.0403201824.5452fbd6@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