Re: Why doesn't this update statement work?

Worked like a charm. Thanks guy!

Tim :-)

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<L687c.5159$Ec6.3156_at_nwrddc02.gnilink.net>...
> "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 Sun Mar 21 2004 - 17:34:22 CET