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 MATCHE

Solution 1:

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.

Solution 2:

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.

Solution 3:

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;

Solution 4:

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

INSERTinto 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.