Skip to content

INSERT-VALUES vs. INSERT-SELECT-FROM-DUAL

September 28, 2010

There’s no difference between the effects of the following two statements, are there:

INSERT INTO mytable (col1, col2) VALUES ('hello','world');
INSERT INTO mytable (col1, col2) SELECT 'hello', 'world' FROM DUAL;

Well, as it turns out, it is possible for the first statement to succeed where the second statement would fail – in the presence of a suitably crafted Before Insert trigger, the second will raise “ORA-04091 table is mutating, trigger/function may not see it”:

http://oraclequirks.blogspot.com/2010/09/ora-04091-table-stringstring-is.html

About these ads

From → PL/SQL, SQL

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 215 other followers

%d bloggers like this: