Search code examples
databaseoracle-databaseinsert-into

Insert query in oracle showing error Missing Expression

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.

Solution

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