Oracle update from select

update query using with clause in oracle

update statement using with clause in oracle

update query using select statement in oracle

oracle with clause update example

Update query using with clause in oracle

Update query in oracle with multiple conditions.

Update statement with exists in oracle

With clause in update statement oracle

Update query in oracle with multiple conditions

With update sql

Update query with if else condition in oracle

WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)

Home » Articles » 12c » Here

In addition to the Subquery Factoring Clause, Oracle 12c includes a PL/SQL declaration section in the clause.

Setup

The examples in this article require the following test table.

DROP TABLE t1 PURGE; CREATE TABLE t1 AS SELECT 1 AS id FROM dual CONNECT BY level <= 1000000; -- Gathering stats on a CTAS is no longer necessary in 12c, -- provided the statement is issued by a non-SYS user.

-- EXEC DBMS_STATS.gather_table_stats(USER, 't1');

Functions in the WITH Clause

The declaration section of the clause can be used to define PL/SQL functions, as shown below.

WITH FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS BEGIN RETURN p_id; END; SELECT with_function(id) FROM t1 WHERE rownum = 1 / WITH_FUNCTION(ID) ----------------- 1 SQL>

Interestingly, the ";" does not seem to work as a terminator to the SQL statement when the PL/SQL declaration is included in the clause.

If we attempt to use it on its own, SQL*Plus waits for more text to be entered

update query to update date in oracle

update query with clause oracle