CODEWITHSUNDEEP
sqloracle-database
Dirk
Dirk

Reputation: 6884

How does Oracle SELECT FROM dual work with multiple fields

Here's the structure of a query I came across:

MERGE INTO TABLE t
USING (SELECT ? id, ? email, ? cr_date, ? info, ? status FROM dual) n
ON t.id=n.id and t.email=n.email
WHEN MATCHED THEN UPDATE SET z.info = n.info z.status='Y'
WHEN NOT MATCHED THEN INSERT (info, status) VALUES (n.info, n.status)

I've read up on using DUAL to return SYSDATE or other operations... is the SELECT simply returning some input values into a table (row) to simplify further operations in the other clauses?

Thanks!

Upvotes: 0

Views: 6188

Answers (4)

Ronnis
Ronnis

Reputation: 12843

The code you came across is meant to update a single row, or create it if it doesn't exist.

DUAL is a special system table containing just one row. Selecting from DUAL is a workaround for Oracles inability to do simply:

select sysdate;

Note that it doesn't have to be dual, it can be any one-row-table or even a query that returns one row.

select sysdate
  from dual;

is equivalent to:

select sysdate
  from my_one_row_table;

and

select sysdate
  from my_table
 where my_primary_key = 1;

Since version 10g, the dual table has a special access path which shows up in the execution plan as "fast dual", which results in 0 consistent gets, which isn't possible to achive on your own using other tables.

Upvotes: 7

N West
N West

Reputation: 6819

The statement appears to be doing essentially an Update Else Insert using a little trick with DUAL. The "?" appear to be bind variables.

The same logic could look like (in pseudocode):

UPDATE t
SET t.id= ?,
    t.email= ?,
    t.status= 'Y'

IF update updated 0 rows THEN

INSERT into t
VALUES (?, ?)

where the two ? are the "info" and "status" variables respectively.

Using DUAL just reduces your work into one database call instead of two.

Upvotes: 0

Senthil
Senthil

Reputation: 5824

Read about DUAL table : http://www.orafaq.com/wiki/Dual It commonly used for SYSDATE, Can be used for multiple fields such as below too: SELECT USER, sysdate from dual;

Upvotes: 0

Ersin Tarhan
Ersin Tarhan

Reputation: 361

a) Yes you can use like this statement. But you must rename your "date" field name cause its keyword of oracle. b) but i stongly recommend to create procedure for this.

Upvotes: 0

Related Questions