Re: SQL Update with a Join Syntax

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 9 Aug 2004 06:48:44 -0700
Message-ID: <2687bb95.0408090548.2d95b083_at_posting.google.com>

jcelko212_at_earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0408071532.174d4354_at_posting.google.com>...
> >> [UPDATE with FROM clause] it's very standard SQL (not that that
> means anything to Oracle). <<
>
>
> Notice that no correlation name is allowed in the UPDATE clause; this
> is to avoid some self-referencing problems that could occur. But it
> also follows the data model in Standard SQL. When you give a table
> expression a correlation name, it is to act as if a materialized table
> with that correlation name has been created in the database. That
> table then is dropped at the end of the statement. If you allowed
> correlation names in the UPDATE clause, you would be updating the
> materialized table, which would then disappear and leave the base
> table untouched.
>

What do you mean that no correlation name is allowed? You can place a table name (correlation) label on an update, otherwise how would you perform co-ordingated subqueries? (Code runs on Oracle 7.0 - 9.2)

update wo_routing a

       set bkf_flag = 'N'
where oper_seq < (select max(oper_seq) from wo_routing b

                  where order_no       = a.order_no
                  and   work_center_no = a.work_center_no
                  and   plant          = a.plant)

and plant = F_plant
and order_no = F_order_no;

Maybe I misunderstand the use of the term in this context.

HTH -- Mark D Powell -- Received on Mon Aug 09 2004 - 15:48:44 CEST