CODEWITHSUNDEEP
sqloracle-database
user1730361
user1730361

Reputation: 31

What does this sql mean

INSERT INTO MAPPING_TBL ( G_ID, MR_ID, G_TYPE, G_NUMBER ) 
SELECT :G_ID AS G_ID, :MR_ID AS MR_ID, :G_TYPE AS G_TYPE, :G_NUMBER AS G_NUMBER 
FROM DUAL WHERE NOT EXISTS 
         (SELECT G_ID, MR_ID, G_TYPE, G_NUMBER 
          FROM MAPPING_TBL 
          WHERE G_ID = :G_ID2 AND 
                MR_ID = :MR_ID2 AND 
                G_TYPE = :G_TYPE2 AND 
                G_NUMBER = :G_NUMBER2 )

Could anyone explain what does this sql actual doing? especially what does this 

SELECT :G_ID AS G_ID, :MR_ID AS MR_ID, :G_TYPE AS G_TYPE, :G_NUMBER AS G_NUMBER 
FROM DUAL

sql do, thanks.

Upvotes: 2

Views: 179

Answers (3)

pilcrow
pilcrow

Reputation: 58524

This is a "conditional insert."

The statement is INSERTing a user-supplied record into a table MAPPING_TBL if that record does not already exist in the table. In MySQL this is accomplished as an 'INSERT IGNORE'. In contemporary Oracle one could use 'MERGE INTO' to the same effect.

The SELECT :G_ID AS G_ID ... FROM DUAL is a way of specifying a tuple of values, in this case parameterized values from the calling program. (If you for instance SELECTed 1, 2, 3, 4 FROM DUAL, you'd get those values back in a row. In this case, the calling program is supplying :G_ID and so on at query execution time.)

Upvotes: 3

codingbiz
codingbiz

Reputation: 26376

SELECT :G_ID AS G_ID, :MR_ID AS MR_ID, :G_TYPE AS G_TYPE, :G_NUMBER AS G_NUMBER 
FROM DUAL

The semi-colon means the user will be prompted to enter those values. They are like parameters that are expected to be filled in at run-time

Upvotes: 0

John Woo
John Woo

Reputation: 263693

It is just a syntax of INSERT INTO...SELECT statement. To make it more simple, take this example below

INSERT INTO table1 (colA, colB, colC)
SELECT colA, colB, colC
FROM table2

What it does is, Whatever rows that are SELECTed from table2 will be INSERTed on table1.

See for more info: Oracle [INSERT INTO...SELECT]

Upvotes: 0

Related Questions