Oracle concat result-CodePudding

I have a table in below format:

Table Name: employee

id	code
1	e1
2	e2

Need help in insert command:

INSERT INTO employee
  (id, code)
VALUES
  (SELECT max(id)   1
     FROM employee,
          SELECT CONCAT('e', (SELECT MAX(id)   1 FROM employee)) FROM dual
   );

am getting an error on this, need help

CodePudding user response：

INSERT INTO EMPLOYEE (ID, CODE)
(Select Nvl(Max(ID), 0)   1 "NEW_ID", 'e' || To_Char(Nvl(Max(ID), 0)   1) "NEW_CODE"
 From   EMPLOYEE)

CodePudding user response：

How about this?

SQL> select * from employee;

        ID CO
---------- --
         1 e1
         2 e2

SQL> insert into employee (id, code)
  2  with temp (max_id) as
  3    (select nvl(max(e.id), 0)   1 from employee e)
  4  select t.max_id,
  5         'e' || t.max_id
  6  from temp t;

1 row created.

SQL> select * from employee;

        ID CO
---------- --
         1 e1
         2 e2
         3 e3

SQL>

Although it works, I'd suggest you NOT to do it this way. MAX 1 works in a single-user environment, but in a multi-user one it'll fail sooner or later because two (or more) users will fetch the same MAX value and try to insert a row, but only the first one who commits will succeed - others will

get an error, if ID is unique (or primary key) (should be, I presume), or
produce duplicates (if there's no uniqueness enforced)

What to do? Use a sequence instead.