Insert query in oracle showing error Missing Expression

Question

insert into ASSET_MAIN_CATEGORIES values(select max(sno) from ASSET_MAIN_CATEGORIES, 'PROD','AC HMU','AC_HMU','PRODUCT','99CS002','','NR','LKO',1);

I wanted to insert max of SNO as column value. But It is showing "Missing Expression Error"

How can I achieve this.

Any Help would be appreciated.

show the table defnition extra columns are getting added up in insert thats why your are getting too many rows. — Nikhil
– Nikhil, Commented Aug 7, 2018 at 14:53
its showing missing expression because sql statement is not within brackets and some ; ... may be required. — Nikhil
– Nikhil, Commented Aug 7, 2018 at 14:55

Kamil Gosciminski · Accepted Answer · 2018-08-07 12:31:54Z

1

There are at least ways of doing it:

INSERT INTO ... VALUES with embedded select in parentheses so that database will evaluate it:

insert into ASSET_MAIN_CATEGORIES values(
  (select max(sno) from ASSET_MAIN_CATEGORIES),
  'PROD','AC HMU','AC_HMU','PRODUCT','99CS002','','NR','LKO',1
);

INSERT INTO ... SELECT since all other data is static:

insert into ASSET_MAIN_CATEGORIES
  select
    max(sno),
    'PROD','AC HMU','AC_HMU','PRODUCT','99CS002','','NR','LKO',1
  from ASSET_MAIN_CATEGORIES
;

Note that if you do not specify which columns you are populating in ASSET_MAIN_CATEGORIES database assumes that you're feeding values for all of them in the order that they were created in this table.

answered Aug 7, 2018 at 12:31

Kamil Gosciminski

17.4k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kamil Gosciminski Over a year ago

@vishnu the problem is with your INSERT statement, and I already wrote on that in the last section of my answer. You are providing more values than you have columns in your table.

Ori Marko · Accepted Answer · 2018-08-07 12:30:58Z

1

You need to surround select max with brackets, because it's a SQL statement:

insert into ASSET_MAIN_CATEGORIES values((select max(sno) from ASSET_MAIN_CATEGORIES), 'PROD','AC HMU','AC_HMU','PRODUCT','99CS002','','NR','LKO',1);

If you have an error, prefer to add column names in insert statement

edited Aug 7, 2018 at 12:30

answered Aug 7, 2018 at 12:24

Ori Marko

59.3k26 gold badges158 silver badges259 bronze badges

1 Comment

Ori Marko Over a year ago

@vishnu so you have more values than columns, you need to show columns and their types, or remove extra value(s), normally in insert you state the column names before values to see avoid such issues . see docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems025.htm

Nikhil · Accepted Answer · 2018-08-09 06:49:33Z

0

this will work :-

i have checked with my tables its working ,sample code to understand:

insert into b(col1,col2) select 7,(select max(col2) from b)
  from dual;

what code you require:-

 insert into ASSET_MAIN_CATEGORIES(col1,col2,....,col10) 
 select (select max(col2) from ASSET_MAIN_CATEGORIES), 'PROD','AC 
 HMU','AC_HMU','PRODUCT','99CS002','','NR','LKO',1)
 from dual;

edited Aug 9, 2018 at 6:49

answered Aug 7, 2018 at 14:53

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

1 Comment

Nikhil Over a year ago

check now its working in col1.col2,..,col 10 you have to pass your column_names that is why i was asking for tables desc from you.

Collectives™ on Stack Overflow

Insert query in oracle showing error Missing Expression

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related